05-20-2016 11:05 AM
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!
05-20-2016 11:17 AM
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.
05-20-2016 11:17 AM
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.
05-20-2016 11:36 AM
05-20-2016 11:44 AM
05-20-2016 11:48 AM
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.