Incompatible Data type - Ucanaccess 5.0

[jorge.galvez]

Hi, I am trying to connect to an MS Access DB file by using Ucanaccess 5.0 driver. I followed step by step the instructions from a popular post here but I have no results. I am getting this error:

java.sql.SQLException: Cannot create PoolableConnectionFactory (UCAExc:::5.0.0-SNAPSHOT tipo de datos incompatibles en la conversión)
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2385)
at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2110)
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1563)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:257)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:211)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.lambda$newRetryRunnable$0(DatasourceManagerImpl.java:1059)
at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:565)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.base/java.util.concurrent.FutureTask.runAndReset(Unknown Source)
at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::5.0.0-SNAPSHOT tipo de datos incompatibles en la conversión
at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:231)
at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:53)
at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:291)
at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:2395)
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2381)
… 12 more
Caused by: java.sql.SQLSyntaxErrorException: tipo de datos incompatibles en la conversión
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.throwError(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.setParameter(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.setObject(Unknown Source)
at net.ucanaccess.converters.LoadJet.execInsert(LoadJet.java:1527)
at net.ucanaccess.converters.LoadJet.access$900(LoadJet.java:74)
at net.ucanaccess.converters.LoadJet$TablesLoader.loadTableData(LoadJet.java:861)
at net.ucanaccess.converters.LoadJet$TablesLoader.loadTableData(LoadJet.java:837)
at net.ucanaccess.converters.LoadJet$TablesLoader.loadTablesData(LoadJet.java:1029)
at net.ucanaccess.converters.LoadJet$TablesLoader.loadTables(LoadJet.java:1077)
at net.ucanaccess.converters.LoadJet$TablesLoader.access$3200(LoadJet.java:264)
at net.ucanaccess.converters.LoadJet.loadDB(LoadJet.java:1579)
at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:218)
… 16 more
Caused by: org.hsqldb.HsqlException: tipo de datos incompatibles en la conversión
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.types.DateTimeType.convertJavaToSQL(Unknown Source)
… 27 more

8.0.16 (b2020082513)
Azul Systems, Inc. 11.0.7

Hope you can give a piece of advise

Thank you!

[KathyApplebaum]

Unfortunately your driver isn’t telling us what datatype is causing the problem, but MS Access is missing a couple of common data types. Two of the most common that seem to cause this problem are the DATE and the TIME datatypes but there might be others that aren’t getting translated correctly by the driver.

[jorge.galvez]

Thank you Kathy. Is any other way or other driver I can go forward?

[KathyApplebaum]

The problem is not the driver, it’s that the datatypes don’t exist in Access. You either need to change what’s being stored or switch to a different database.

[Kevin.Herron]

What translator settings did you use? The validation query might be SELECT CURRENT_TIMESTAMP and that might not be supported by Access. You might need a custom translator that uses SELECT 1 or something instead.

It could also be a bug in the Ucanaccessdriver.

Another approach would be to use MS SQL Server and its Linked Server feature, which can link to an Access DB.

[jorge.galvez]

Thank you Kevin. I am using MSSQL as translator. As validation query I am using SELECT 1
I will take a look into the Linked Server feature from MS SQL and give it a try.

[jorge.galvez]

Hello. Finally I got it with Linked Server feature using both ODBC driver and MS Access 12.0 Driver separately. Here I will leave how to do it with ODBC driver.
First, an ODBC DNS pointing to the MS Access file must be configured on the System DSN tab:

It is important to set security configuration of the service MSSQLSERVER properly, which means check “Local system account” in the security tab if the connection is.

Check the .mdb file is set to be shared in MS Access configuration.

Add your linked server and select ODBC as follows:

Then type the ODBC name exactly as you created in at the begining. Select provider as shown in the picture:

In SQL Server Management Studio uncheck the MSDASQL “Allow inprocess” option located in the Providers folder.

Then, if not exist, create a DB just to let Ignition validate the connection.

In Ignition, set the MS SQL connection properly and indicate the database name previously configured:

Finally, the SQL syntax to fetch data from the linked server should be something like this by using openquery to build the SQL query to the linked server resource:

select * from openquery(
    [Linked_server_name],
    'select * from [table_name]')

In my application it looks like this: