Hello All,
I am currently working on a project that provides a front-end for a variety of different types of database at a production facility. The ignition gateway is configured to interface with MSSQL and MySQL databases, and we have been utilizing those database connections without any issues.
However, the last piece of this puzzle is a Microsoft Access database. We have been supplied with the *.mdb file with which to create an ODBC Data Source, but have had some trouble getting Ignition to communicate with this DSN.
The Gateway is running on a 64 bit machine. Microsoft’s ODBC Data Source Administrator, as is, does not have any drivers to create Microsoft Access ODBC Data sources for 64 bit Java applications. As such, we decided to do some searching to see if those drivers were available for download anywhere. We were able to find one on Microsoft’s website, so we downloaded and installed the engine with no issues. After installation, we were able to configure the gateway to connect to the Access database. I was able to open the designer and do some basic querying, binding a few database tables to some table components. The drivers did seem a little bit buggy (For example, it was impossible to query in the designer’s database browser but queries executed without any issues when bound to a dataset on a component), but seemed as if they would work for our purposes (basic reporting using a few simple queries).
This was all done on a Friday, and following the weekend on Monday, the database connection became faulted and has been stuck there ever since. The error message it is returning is
Cannot create PoolableConnectionFactory ([Microsoft][ODBC Driver Manager] Invalid string or buffer length).
I’ve tried deleting the Datasource through windows, removing the connection from the gateway, and re-adding both of them but to no avail. The connection always immediately goes to “Faulted” and I can’t seem to figure out why.
I did install a trial version of Ignition on a different PC, as well as the same 64 bit drivers for Microsoft Access ODBC data source. On this computer, I was able to once again connect to and query the Access database (a copy of the *.mdb mentioned previously). Occasionally that same error message would surface as a red popup box in the designer (when querying certain tables, for example), but the database connection on the gateway was not getting stuck as “Faulted” as it was on the other PC.
So my question, after that small novel, is what exactly does that error message about the invalid string or buffer length actually mean? Why would it be intermittent and only occur when querying some tables on the second PC, but persistent and occurring all the time on the first PC?
Secondly, is there any way to connect to the Access database using a 32 bit ODBC driver, if the the gateway itself is running via 64 bit Java? All of our attempts so far threw an error concering architecture mismatch, as expected.