I’m trying to connect to a MS Access database with Ignition 7.8 and have been unsuccessful. All the examples I’ve found have been pre-Java 8 and the elimination of the ODBC-JDBC bridge.
I’ve been trying to use the UCanAccess driver (http://ucanaccess.sourceforge.net/site.html) but haven’t been able to get it to work.
Anyone have any experience with this?
I was able to get this to work. Attached screenshot of the driver setup. Also, need to make sure to include all the dependent .jar files in addition to the ucanaccess jar.
Hi ted_s,
I’m trying to get this to work but not having any luck. Your screenshot of the driver setup is not available any more, could you please repost it?
I’ve tried quite a few combinations of settings, and the Database connection either says “Connecting” forever, or I get this error:
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (UCAExc:::4.0.2 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:224)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:180)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer$1.run(DatasourceManagerImpl.java:1037)
at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:593)
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:::4.0.2 unexpected end of statement
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:210)
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.2 (b2017041315)
Oracle Corporation 1.8.0_131
Any help appreciated.
Thanks Andrea,
I’ve tried again with the settings in your screenshots but still having no luck. I’ve tried a couple of different database files and I keep getting the same error as in my previous email.
Any other suggestions?
Cheers,
David
Hello
Have you insert all the jar files of ucanaccess like the my firts picture?
moreover in my project I have used the version 3.04 of the ucanaccess, maybe this suggestion can help you
Andrea
Thanks Andrea,
Yes, I’ve got all the jar files as shown below. I’ll try version 3.04 and let you know how it goes.
Thanks Andrea, Got it working!
When I first changed the driver to version 3.04 it didn’t work, but once I deleted the database connection and recreated it, it worked straight away!
I am attempting the exact same setup except my Access Database is on a remote PC.
I am trying to use the following connection URL (jdbc:ucanaccess:////ERICK-PC/VisuaLCN/Data/SVRLATestData.mdb)
As the file is on PC name “ERICK-PC”
And within that PC the file is located at C:\VisuaLCN\Data\SVRLATestData.mdb
I can navigate to this file from any PC; as in it is shared properly.
I have all the jar files and the exact releases (ie ucanaccess-3.0.4 etc)
Any ideas?
I forgot to post the errors I am getting…
(and FYI I have also tried with only 2 forward slashes to no avail.)
the error is:
Cannot create PoolableConnectionFactory (UCAExc:::3.0.4 given file does not exist: \ERICK-PC\VisuaLCN\Data\SVRLATestData.mdb)
You may have to set up a mapped drive before you can get this working. This thread covers how to make changes to the ignition.conf file to map a drive that the gateway has access to.
Once you’ve done that you can start trying different paths that use the mapped drive.
I stopped using the mapped drive for our organization and stick to UNC paths for files in our scripts. If you ensure the Ignition Gateway service runs as a domain user (we have a dedicated service account to run Ignition) then your scripting can access any shares that the associated domain account has been granted access to. This approach works well for us because there is no addition changes to the Ignition.conf and new access is done simply by granting the domain account access to the share.
Kevin,
I have added the following in the ignition.conf file.
wrapper.share.1.location=\ERICK-PC\VisuaLCN\Data
wrapper.share.1.target=W:
wrapper.share.1.type=DISK
wrapper.share.1.account=omega
wrapper.share.1.password=7420
wrapper.share.1.startup.premapped=CONTINUE
wrapper.share.1.startup.max_retries=5
wrapper.share.1.startup.retry_interval=10
here is the message I get back in the wrapper.log file.
Attempting to map the “\ERICK-PC\VisuaLCN\Data” share to “W:”…
WARN | wrapper | 2017/06/08 15:11:11 | Running a service as System User disqualifies the use of an account. Ignoring wrapper.share..account
WARN | wrapper | 2017/06/08 15:11:46 | Unable to map “W:”. Trying to continue. (The network path was not found. (0x35))
A question I have is, do I need the W drive mapped in windows as well? Currently I do not have W:\ mapped by Windows.
However, in another test I did use a Drive (Y:) that is mapped as windows and I received a similar error although slightly different.
Attempting to map the “\192.168.0.103\Data” share to “Y:”…
WARN | wrapper | 2017/06/08 14:31:34 | Running a service as System User disqualifies the use of an account. Ignoring wrapper.share..account
WARN | wrapper | 2017/06/08 14:31:34 | Unable to map “Y:”. Trying to continue. (The network resource type is not correct. (0x42))
What is ‘running a service as System User’ mean and how do I get around this?
Thanks Andrea, but I was only able to read through your method, but it does not write to the access database, have you found solution to writing to the database yet?
Niresh
Hi David.
I have download the UCanAccess-4.0.3 rigth now, then i want to installa the UCanAccess like a New JDBC Driver, but i dont find information about this procedure into the manuals, may help me about how to install the UCanAccess and all the related drivers?, Thanks.
Regards
Gerardo Garcia
Try installing SQL Server Express and add the MS Access database as a linked server. On the gateway, make a DB Connection to sql express.
Thanks JPark, great solution.
Thanks Andrea, very usefull screenshots. I have sucessfully connected to the MSAccess DB
Also many thanks Andrea…I was able to get a valid connection using your advice. We’ll see how it goes in the Ignition Designer but I am hopeful that I can display some Access table info.
One thing to note for others who may use this method:
For the default validation query…use SELECT 1 FROM DUAL as shown in the screenshot…do not use SELECT 1 (default)…doesn’t work.
Hey, I can confirm this is an issue with ucanaccess. pyodbc works just fine to update an MS Access database.
