MS Access 64 Bit ODBC Driver

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.

1 Like

Believe it or not, a restart of the server caused the Database Connection to the Access database to go from Faulted to Valid. No signs yet as to what may have caused the connection to become stuck in the faulted state to begin with, but I’ll be keeping an eye on it to see if some sort of pattern arises.

How did you install the engine?
I am running Ignition 7.9.9, and only the JDBC driver option is available, there’s none for an ODBC driver.

Not sure what they did in 2013, but this seems promising: http://ucanaccess.sourceforge.net/site.html

You’ll need to add it as a new DB driver type:
https://docs.inductiveautomation.com/display/DOC79/JDBC+Drivers+and+Translators#JDBCDriversandTranslators-AddingaJDBCDriver

Thanks for your reply.

I am getting this error when I added the driver:

AbstractRecordEditPanel 22Aug2018 11:01:01 Error updating JDBC Driver “UcanAccess 4.0.4”.

simpleorm.utils.SException$Jdbc: Executing INSERT INTO JDBCDRIVERS (JDBCDRIVERS_ID, Name, Description, DefaultTranslatorId, Classname, URLFormat, DefaultProps) VALUES (?, ?, ?, ?, ?, ?, ?) for [JDBCDriverRecord 1 NewRecord Dirty0]
at simpleorm.sessionjdbc.SSessionJdbcHelper.flushExecuteUpdate(SSessionJdbcHelper.java:434)

Caused by: org.sqlite.SQLiteException: [SQLITE_CONSTRAINT_PRIMARYKEY] A PRIMARY KEY constraint failed (UNIQUE constraint failed: JDBCDRIVERS.JDBCDRIVERS_ID)

The Ignition gateway is running on a Ubuntu virtual machine on a Windows 10 Host (not sure if this is the problem).