Look up external value based on field on document

[Kris_Conrads]

In a field recognition zone, can I use a script to look up a value in an external SQL database based on a field on the document?

I am trying to set up a way to automatically index W-2 forms.  I'm not particularly fond of storing a Social Security Number if I don't absolutely have to.  We use employee ID instead.  What I'd like to do is use the script to set the OCRDoc.FieldText value based on querying our LOB database.  But, I can't seem to get the connection string to work.

Here's a bit of the code...

Dim DBConnection, ConnectionString, EmpID, SQLQuery
SQLQuery = "SELECT emp_no FROM goldstandard WHERE ssn = '" & OCRDoc.FieldText & "''"
ConnectionString = "DSN=Eden;Uid=username;Pwd=password"
Set DBConnection = CreateObject("ADODB.Connection")
DBConnection.Open ConnectionString
Set EmpID = CreateObject("ADODB.Recordset")
EmpID.Open SQLQuery, DBConnection
OCRDoc.FieldText = EmpID.Fields("emp_no").Value

When I use the DSN Name as above or when I pass the driver, database name, server name, etc. I get error 80040e37 invalid object name.  I know the DSN works on the machine because it is the same one the Thick client uses.

Any suggestions?  Is this even possible?

Thanks so much!

[Steve_Reed · Accepted solution]

If you're running in a 64-bit operating system, then the VB script is probably being executed by the 64-bit version of the Microsoft script host - which means it will look for a 64-bit ODBC source (instead of the 32-bit ODBC source that the OnBase client itself is using).  Check by running odbad32.exe in the windows\system folder (not the one in windows\syswow64) and make sure you have the same ODBC source (with the same name "Eden" as the 32 bit version) connecting to the same database.

[Kris_Conrads]

I created a 64-bit ODBC connection and used that DSN instead. Now the Verification Report shows "The specified DSN contains and architecture mismatch between the Driver and Application".

[Steve_Reed]

Ah ok - then it wasn't the 32-bit versus 64-bit issue then (i.e. go back to using the 32 bit ODBC source) - maybe the problem is in the SQL query itself. Perhaps the 'invalid object' that it is referring to is the table name in the query - try fully qualifying it (i.e. use dbo.goldstandard instead of just goldstandard - or whatever the table owner is if not 'dbo')

[Steve_Reed]

or even use the convention owner.database_name.table if the ODBC source configuration isn't set up to connect to a specific database on that server.