# Incompatible Data type - Ucanaccess 5.0 **URL:** \ **Category:** Ignition\ **Tags:** ignition80\ **Created:** [September 25, 2020, 2:26pm UTC](https://forum.inductiveautomation.com/t/incompatible-data-type-ucanaccess-5-0/39063 "2020-09-25T14:26:30Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![jorge.galvez](https://avatars.discourse-cdn.com/v4/letter/j/ecb155/32.png) [@jorge.galvez](https://forum.inductiveautomation.com/u/jorge.galvez)\ **Post date:** [September 25, 2020, 2:26pm UTC](https://forum.inductiveautomation.com/t/incompatible-data-type-ucanaccess-5-0/39063/1 "2020-09-25T14:26:30Z")
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! ---
**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\ **Post date:** [September 25, 2020, 3:17pm UTC](https://forum.inductiveautomation.com/t/incompatible-data-type-ucanaccess-5-0/39063/2 "2020-09-25T15:17:38Z")
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. ---
**Author:** ![jorge.galvez](https://avatars.discourse-cdn.com/v4/letter/j/ecb155/32.png) [@jorge.galvez](https://forum.inductiveautomation.com/u/jorge.galvez)\ **Post date:** [September 25, 2020, 3:21pm UTC](https://forum.inductiveautomation.com/t/incompatible-data-type-ucanaccess-5-0/39063/3 "2020-09-25T15:21:19Z")
Thank you Kathy. Is any other way or other driver I can go forward? ---
**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\ **Post date:** [September 25, 2020, 3:36pm UTC](https://forum.inductiveautomation.com/t/incompatible-data-type-ucanaccess-5-0/39063/4 "2020-09-25T15:36:16Z")
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. ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [September 25, 2020, 3:47pm UTC](https://forum.inductiveautomation.com/t/incompatible-data-type-ucanaccess-5-0/39063/5 "2020-09-25T15:47:19Z")
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. ---
**Author:** ![jorge.galvez](https://avatars.discourse-cdn.com/v4/letter/j/ecb155/32.png) [@jorge.galvez](https://forum.inductiveautomation.com/u/jorge.galvez)\ **Post date:** [September 25, 2020, 3:54pm UTC](https://forum.inductiveautomation.com/t/incompatible-data-type-ucanaccess-5-0/39063/6 "2020-09-25T15:54:07Z")
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. ---
**Author:** ![jorge.galvez](https://avatars.discourse-cdn.com/v4/letter/j/ecb155/32.png) [@jorge.galvez](https://forum.inductiveautomation.com/u/jorge.galvez)\ **Post date:** [October 12, 2020, 1:27am UTC](https://forum.inductiveautomation.com/t/incompatible-data-type-ucanaccess-5-0/39063/7 "2020-10-12T01:27:01Z")
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: ![ODBC](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/1/11cea457281e98cd31e6ae5e5c218974a6e81625.png) 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. ![MSSQLSERVER](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/6/06fbe0a771309f6feba1bd0efb937ce56951e1ea.png) Check the .mdb file is set to be shared in MS Access configuration. ![AccessConfig](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/8/e874d94e145efb534be1694284ff63fddff67ce6.png) Add your linked server and select ODBC as follows: ![Linked_1](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/a/3af230caeda1be2ae163b78c6f63d82a97b12ef5.jpeg) Then type the ODBC name exactly as you created in at the begining. Select provider as shown in the picture: ![Linked_2](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/7/37b04efff6a3c020d80013d79acd3fccebcea5b2.jpeg) In SQL Server Management Studio uncheck the MSDASQL “Allow inprocess” option located in the Providers folder. ![Allow Inprocess](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/d/1d22f7e555163d0925071c8b7b0372799b87e4ad.png) Then, if not exist, create a DB just to let Ignition validate the connection. ![DB object](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/2/820e4ec4a7146e5577e64061739e88613f8f2ddd.png) In Ignition, set the MS SQL connection properly and indicate the database name previously configured: ![Ignition MSSQL config](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/2/023848b49b7102ff278eefc63a12892cb82f1529.png) 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: ```auto select * from openquery( [Linked_server_name], 'select * from [table_name]') ``` In my application it looks like this: ![SQL Browser](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/d/adb29354b7348ce9e61d9ef4c0bc0ba61738e28f.png)