Getting MS Access to work...

KNIME Analytics Platform
1

Hi,

I'm failing at step 0 in getting MS Access to work (reliably at least). I've reviewed the following:

The FAQ: https://tech.knime.org/faq#q21 (which seems to be out of date, as it still references ODBC access);

A couple of very helpful forum posts:

https://tech.knime.org/node/20814 and https://tech.knime.org/forum/knime-general/no-connection-to-ms-access-database

First, when adding files or directories via KNIME > Preferences > Databases, the file or directory doesn't show up in the list until the second time through - is it supposed to be this way?

Second, when UcanAccess is loaded, I get the following error via the various log files:

!ENTRY org.eclipse.ui 4 0 2017-05-05 16:15:02.506
!MESSAGE Unhandled event loop exception
!STACK 0
java.lang.NullPointerException
    at net.ucanaccess.jdbc.UcanloadDriver.getMajorVersion(UcanloadDriver.java:129)
    at org.knime.core.node.port.database.DatabaseWrappedDriver.getInfo(DatabaseWrappedDriver.java:155)

Looking at the UcanloadDriver code, this may be related to a need to set environment variables for UCanAccess to work correctly? It is accompanied by the following error via the console:

ERROR KNIMECorePlugin                 FATAL: error initializing KNIME repository - check plugin.xml and classpath

Finally, when I configure an Access 2016 database, and click the Fetch Metadata button, nine times out of ten, the connection to the database times out as follows (even with a timeout set to 60s):

2017-05-05 15:43:21,297 : WARN  : SwingWorker-pool-6-thread-2 : DBReaderDialogPane : Database Reader : 2:1 : Error during fetching metadata from database, reason: java.io.IOException: Connection to database 'jdbc:ucanaccess://C:/CDA_Downloads/2014_Wells.accdb;memory=false' timed out

Once in a while, the exact same configuration works, and I'm a happy KNIME user. Would really like to get it working the other 9 out of 10 times too!

This is all on 64 bit Windows 7, with a fresh clean current installation of KNIME, and UCanAccess 4.0.2. I tried the 3.0.7 version, but no different.

UCanAccess jars copied to C:\Program Files \KNIME\jre\lib\ext:

05/05/2017  15:59           284,220 commons-lang-2.6.jar
05/05/2017  15:59            60,031 commons-logging-1.1.1.jar
05/05/2017  15:59         1,467,326 hsqldb.jar
05/05/2017  15:59           855,824 jackcess-2.1.6.jar
05/05/2017  16:16           367,701 ucanaccess-4.0.2.jar

Does anyone have a recipe for getting KNIME up and running, reliably, using the current toolset, or any suggestions on what I might be missing? I've tried all the obvious (to me at least) things - very puzzled, as I thought this would be the easy first step in my KNIME adventure!

Thank you.

Dan.

 

2

So - the following is slightly messy, but works for me.

  1. Copy the following JARs into <KNIME>/jre/lib/ext:

    05/05/2017  16:16           284,220 commons-lang-2.6.jar
    05/05/2017  16:16            60,031 commons-logging-1.1.1.jar
    05/05/2017  16:16         1,467,326 hsqldb.jar
    05/05/2017  16:16           855,824 jackcess-2.1.6.jar
    11/05/2017  09:20           364,298 ucanaccess-4.0.2.jar
  2. Open KNIME, and navigate to File > Preferences > KNIME > Databases
  3. Click Add file, and select <KNIME>/jre/lib/ext/ucanaccess-4.0.2.jar. Click Open.
  4. Repeat step 3. For me, the file doesn't show in the List of loaded database driver files the first time through.

Do NOT copy over ucanload.jar, for reasons I'll come on to shortly.

This gets me to the point that KNIME loads the UCanAccess JDBC drivers. Now comes the messy bit. On closing KNIME and re-opening, I get the following error in the console:

FATAL: error initializing KNIME repository - check plugin.xml and classpath

In the logs, this relates to an error that occurs in how UCanAccess tries to bootstrap itself:

java.lang.NullPointerException
 at net.ucanaccess.jdbc.UcanloadDriver.getMajorVersion(UcanloadDriver.java:129)

The issue appears to be that KNIME goes looking for all the JDBC drivers it can find in the UCanAccess jar, and it turns out there are two: UcanaccessDriver, and UcanloadDriver. All UCanloadDriver does is try to return UcanaccessDriver - but it fails, probably in the static code it uses to try to figure out its home environment.

UCanAccess is shipped with UcanloadDriver in a separate JAR, but for some reason it is also included in the main ucanaccess-4.0.2 JAR as well. The quick and dirty way to fix this is to open ucanaccess-4.0.2.jar in 7-Zip, or something like that, and delete the UcanloadDriver.class and UcanloadDriver$1.class files in net/ucanaccess/jdbc.

Then, all loads as it should, with no fatal errors, and KNIME works happily with MS Access.

If anyone has a non-kludgy way of getting KNIME started using the standard UCanAccess build, and without any fatal errors on startup, would love to know how it's done! Packaging this properly with the standard KNIME build certainly gets my vote.

Dan.

3

Hi,

please have a look at this post for a description on how you should setup UCanAccess:

https://www.knime.com/forum/knime-general/how-to-connect-to-ms-access-database

You should not use the Add files but the Add directory button.

Bye

Tobias