Ignition and Microsoft Access - connection valid now

Hello Ignition Community,

I’m using Ignition 7.9.7 and am tasked with connecting to a MS Access database (.accdb)…yes, I’d much rather it was MSSQL but the guy who has the Access db will not change it. Options to install SQL Server Express and use a Linked Server or to purchase a JDBC-ODBC bridge seem like a lot of extra work and/or money. I tried both ucanaccess version 4.0.4 and 3.0.4 but to no luck using this excellent forum post:

following the post above, everything seems to work fine except when I make my database connection, Ignition takes forever to connect and then gives the following error message (for 3.0.4) here:

#######################

org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (UCAExc:::3.0.4 unexpected end of statement)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:242)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:196)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer$1.run(DatasourceManagerImpl.java:1014)
at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:565)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask.runAndReset(Unknown Source)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.4 unexpected end of statement
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:211)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at org.apache.commons.dbcp.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:332)
at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1249)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)
… 12 more
Caused by: java.sql.SQLSyntaxErrorException: unexpected end of statement
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:208)
… 16 more
Caused by: org.hsqldb.HsqlException: unexpected end of statement
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
at org.hsqldb.ParserDQL.XreadSelect(Unknown Source)
at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
at org.hsqldb.Session.executeDirectStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
… 19 more

7.9.7 (b2018032914)
Oracle Corporation 1.8.0_172

######################

Not sure if I need a specific version of ucanaccess to work with Ignition version 7.9.7 or if there is something that I’m missing…it seemed pretty straight forward…here’s what I did (for version 4.0.4…did same thing with 3.0.4 and got the same result)

  1. Download ucanaccess version 4.0.4 from the following:

https://sourceforge.net/projects/ucanaccess/files/

  1. Extract the zip files to a directory you will be using (i.e. MyDocuments)

  2. extract ucanaccess-4.0.4.jar using WINRAR or https://extract.me/ (this creates a “net” and “META-INF” directory)

  3. Create a new Database Driver in Ignition from the Configure menu

Name: MSAccessDB

add the following JAR files

C:\Users\xxxx\Documents\AccessDB\UCanAccess-4.0.4-bin\lib\commons-lang-2.6.jar;
C:\Users\xxxx\Documents\AccessDB\UCanAccess-4.0.4-bin\lib\commons-logging-1.1.3.jar;
C:\Users\xxxx\Documents\AccessDB\UCanAccess-4.0.4-bin\lib\hsqldb.jar;
C:\Users\xxxx\Documents\AccessDB\UCanAccess-4.0.4-bin\lib\jackcess-2.1.11.jar;
C:\Users\xxxx\Documents\AccessDB\UCanAccess-4.0.4-bin\ucanaccess-4.0.4.jar;

Classname: net.ucanaccess.jdbc.UcanaccessDriver

Driver type: Generic

URL Format: jdbc:ucanaccess://

default validation query: do not use SELECT 1…this needs to be SELECT 1 DUAL (very important does not work otherwise)

default translator: MSSQL

  1. Add database Connection in Ignition

Name: RepairsDB

Connect URL: jdbc:ucanaccess://C:\xxxxx\yyyyy.accdb

#############

anyway, any helpful guidance would be much appreciated.

FOLLOW - UP 11/26/2018

I changed the “Default Validation Query” to “SELECT 1 FROM DUAL” from “SELECT 1” as was shown in the example and able to query Access databases in Ignition…this works great!!! :slight_smile: