No connection to MS Access Database

KNIME Analytics Platform
1

Hi,

I have a problem to connect KNIME with my MS Access Database.

I get the following error message:

ERROR Database Connector   0:1        Execute failed: Could not create connection to database: Driver "org.sqlite.JDBC" does not accept URL: jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Workspace\Datenbanken\SurgicalManager.mdb

My configuration is:

  • Windows 7 (64-bit)
  • KNIME (v3.1.1.v201512111505)
  • Microsoft Access Database Engine 2010 Redistributable
  • I installed my MS Access Driver (32-bit) at: C:\Windows\SysWOW64\odbcad32.exe
  • installed the Microsoft Access Driver (*.mdb, *.accdb)

Any ideas what is going wrong?

Kind regards,

Sevenhills

 

 

2

Are you using 32 bit KNIME? I guess the 32 bit JDBC driver only works with 32 bit KNIME.

3

Hi,

yes, I do.

4

From the error msg it seems like you selected the SQLite driver, not the Access one.

Cheers,
E

5

Hello Sevenhills,

KNIME 3.x runs with Java 8 and unfortunately Oracle has removed the standard JDBC-ODBC driver from the Java VM starting with Java 8. They encourage people to use vendor specific jdbc drivers instead. For alternatives have a look at the stackoverflow conversation about this topic.

One alternative is to use UCanAccess. To use UCanAccess in KNIME you have to copy the required jar files (ucanacces-xxx.jar, jackcess-xxx.jar, commons-lang-xx.jar, commons-logging-xxx.jar,hsqldb.jar) into <KNIME_INSTAL>\jre\lib\ext and register the driver jar (<KNIME_INSTAL>\jre\lib\ext\ucanacces-xxx.jar) as described in the database documentation. You should then be able to connect to your MS Access database via the Database Connector node by selecting net.ucanaccess.jdbc.UcanaccessDriver as database driver and using a database url similar to jdbc:ucanaccess://<full path to your mdb file>;showschema=true

Bye,

Tobias

2 Likes
6

Very useful, thanks Tobias!

-E

7

Hi Tobias,

thank you very much for your help. It works great!

Sorry for my late answer.

Kind regards,

Sevenhills

 

8

I already downloaded ucanaccess.jar, but when I try register in Preferences>Knime>Database and click above this archive it doenst show up in the window LIST OF LOADED DATABASE DRIVE FILES.

9

Hello,

for read only acces you can register the ucanload.jar file from the loader directory in the Preferences>Knime>Database. To open a MS Access file drag and drop the Database Connector Node onto your workflow and open its configuration dialog. In the dialog select net.ucanaccess.jdbc.UcanloadDriver from the list of available datbase drivers and use the following Database URL: jdbc:ucanaccess://C:/temp/Access2010DB/Books2010.accdb;showSchema=true whereby you need to replace C:/temp/Access2010DB/Books2010.accdb with the path to your MS Access db.

Bye

Tobias

 

10

Hello everyone,

for clarification you can find attached an example workflow that demonstrates the usage of the UCanAccess loader driver. To execute the you first have to register the ucanloader.jar file as described in my previous post.

Bye

Tobias

11

Thank you Tobias for the answer!

I connected Knime to MS Acess with success !!! :)

But now I have a new challenge, I´m trying export data from Knime to MS Access using the same logic.

When I export text data everything works very well, but when I try export value data, example total of sales with the format double precision ,the Knime generated this error : ERROR Database Writer      0:681      Execute failed: UCAExc:::3.0.5 unexpected token: Total of sales

Do you know how can I fix it?

Thank you once again!

William Paiva

 

 

 

12

Hello,

the error is thrown by the UCanAccess driver it seems because of the two spaces as described here.

Bye

Tobias

13

Thank you!

It works

14

Hello, 
I'm trying to read Access databases with Knime (V3.3) at a Windows 2012R2 server without any success even following these descriptions.

I'm losing my external db driver configuration when I added the UCanAccess driver to them.
I also got no selection in the driver list for ucanaccess

I found during searching the following error description 

(https://sourceforge.net/p/ucanaccess/discussion/general/thread/aa88b28d/?limit=25)

 

Any idea

Thanks for helping

15

I think your are not using Ucanaccess try with ucanaccess

 

http://ucanaccess.sourceforge.net/site.html

DABASE DRIVE:

net.ucanaccess.jdbc.UcanacessDriver

16

Try this for large *.accdb files (tested in a file of 1.1Gb ) using Ucanaccess

Download Ucanaccess -> http://sourceforge.net/projects/ucanaccess/files/

Extract the .zip

Setup:

check if knime.ini file have the value  -Xmx1G(1024m) or more

GO TO: Files-> Preferences->KNIME->Databases

click "Add directory" and select "bin" and "loader" folders into Ucanaccess

click "Add file" and select ucanaccess-3.0.7.jar

set  "Timeout for database operation (in seconds)" in 60

Apply and Ok

Now

select Database Connector Node

Database Driver:   net.ucanaccess.jdbc.UcanloadDriver

Database URL:      jdbc:ucanaccess://C:\path\my_database.accdb;memory=false;showschema=true

Change the path with your *.accdb path

 memory=false is important if you don't have much memory

apply the change and run the node

17

Hi Everyone,

 

I'm new to KNIME, Please help me in writing data to Access DB.

I followed the steps advised by tobias.koetter to connect to Access DB and it woks superb, now I need to write the data back. Please give me the steps to be followed.

 

 

Thank you guys.

 

18

Hi,

 

Please someone help me in writing the data back to MS Access databse.

I'm getting this error ERROR Database Writer      2:19       Execute failed: UCAExc:::4.0.1 unexpected token: STATE required: (  for the workflow which I'm attaching.

 

Thank you

 

19

Tatan,

Thanks for the clear instructions on how to get KNIME up and running with MS Access.

I'm afraid I've run into a couple of snags, however, from a clean, current KNIME and UCanAccess 4.0.2 install:

I extracted UCanAccess into C:\Java\ucanaccess on my 64-bit Windows 7 PC, and then added the two directories and the file as you suggest.

On starting up KNIME, I got the following ClassNotFoundException:

2017-05-09 09:53:06,788 : DEBUG : main : DatabaseDriverLoader :  :  : Could not create instance of JDBC driver class 'net.ucanaccess.jdbc.UcanaccessDriver': com.healthmarketscience.jackcess.util.ErrorHandler
java.lang.ClassNotFoundException: com.healthmarketscience.jackcess.util.ErrorHandler

If I copy the jars in the UCanAccess lib directory into <KNIME>\jre\lib\ext, that goes away; but then I get the following when the driver loads:

2017-05-09 09:54:09,542 : DEBUG : main : KNIMECorePlugin :  :  : FATAL: error initializing KNIME repository - check plugin.xml and classpath
java.lang.NullPointerException
 at net.ucanaccess.jdbc.UcanloadDriver.getMajorVersion(UcanloadDriver.java:129)

Is there anything else you've set up in your Java or UCanAccess environment that needs replicating across to KNIME? From a quick look through the UcanloadDriver.java source, this looks like maybe an environment variable issue; but as I presume others are using MS Access trouble free, it is perhaps just something obvious I've missed here?

Thank you.

Dan.

20

After not showing me the Ucanload.jar i just added every .jar file from the ucanaccess link tobias mentioned above. After then i saw in the database connector node the .netucanaccess.jdbc.ucanaccessdriver =)