How to connect to MS Access Database

KNIME Analytics Platform
1

Hi,

here are the steps you need to perform to connect to a MS Access database with KNIME Analytics Platform:

  1. Download the UCanAccess JDBC driver by clicking on this link
  2. Extract the downloaded zip file to a folder on your local hard drive
  3. Create a new folder e.g. UCanAccess on your hard drive
  4. Copy from the extracted folder the following files into the new created folder (see driverFiles.PNG):
    • ucanaccess-4.0.3.jar (located in the root folder)
    • commons-lang-2.6.jar (located in the lib folder)
    • commons-logging-1.1.3.jar (located in the lib folder)
    • hsqldb.jar (located in the lib folder)
    • jackcess-2.1.9.jar (located in the lib folder)
  5. Open KNIME Analytics Platform
  6. Go to File->Preferences
  7. In the preferences window navigate to KNIME->Databases
  8. Add the new created folder e.g. UCanAccess to the driver files via the Add directory button (see KNIMEpreferences.PNG)
  9. Close the preferences window with OK
  10. Use the Database Connector node to establish a connection by using the newly added net.ucanaccess.jdbc.UCanaccessDriver driver (see nodeDialog.PNG)

For further details about available JDBC parameters and SQL syntax have a look at the UCanAccess documentation. Attached you can also find an example workflow that should help you to get started.

Bye

Tobias

 

 

1 Like
2

Hi Tobias,  My UCanAccess driver stopped working in Knime.  I reinstalled the latest UCanAccess driver per above instructions without success.   When added the driver directory to Preferences as shown in step 8 I receive the following KNIME error->  

eclipse.buildId=unknown
java.version=1.8.0_152
java.vendor=Oracle Corporation
BootLoader constants: OS=win32, ARCH=x86_64, WS=win32, NL=en_US
Command-line arguments:  -os win32 -ws win32 -arch x86_64

org.eclipse.ui
Error
Tue Jan 09 11:35:24 MST 2018
Unhandled event loop exception

java.lang.VerifyError: Cannot inherit from final class
    at java.lang.ClassLoader.defineClass1(Native Method)
    at java.lang.ClassLoader.defineClass(ClassLoader.java:763)
    at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142)
    at java.net.URLClassLoader.defineClass(URLClassLoader.java:467)
    at java.net.URLClassLoader.access$100(URLClassLoader.java:73)
    at java.net.URLClassLoader$1.run(URLClassLoader.java:368)
    at java.net.URLClassLoader$1.run(URLClassLoader.java:362)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:361)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    at org.knime.core.node.port.database.DatabaseDriverLoader.loadClass(DatabaseDriverLoader.java:394)
    at org.knime.core.node.port.database.DatabaseDriverLoader.readZipFiles(DatabaseDriverLoader.java:286)
    at org.knime.core.node.port.database.DatabaseDriverLoader.readDir(DatabaseDriverLoader.java:255)
    at org.knime.core.node.port.database.DatabaseDriverLoader.loadDriver(DatabaseDriverLoader.java:234)
    at org.knime.core.node.port.database.DatabaseDriverLoader.loadDriver(DatabaseDriverLoader.java:225)
    at org.knime.workbench.ui.database.DatabaseDriverListEditor.getNewInputObject(DatabaseDriverListEditor.java:186)
    at org.eclipse.jface.preference.ListEditor.addPressed(ListEditor.java:102)
    at org.eclipse.jface.preference.ListEditor.access$1(ListEditor.java:100)
    at org.eclipse.jface.preference.ListEditor$1.widgetSelected(ListEditor.java:177)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:249)
    at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
    at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4410)
    at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1079)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4228)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3816)
    at org.eclipse.jface.window.Window.runEventLoop(Window.java:818)
    at org.eclipse.jface.window.Window.open(Window.java:794)
    at org.eclipse.ui.internal.dialogs.WorkbenchPreferenceDialog.open(WorkbenchPreferenceDialog.java:211)
    at org.eclipse.ui.internal.OpenPreferencesAction.run(OpenPreferencesAction.java:63)
    at org.eclipse.jface.action.Action.runWithEvent(Action.java:473)
    at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:565)
    at org.eclipse.jface.action.ActionContributionItem.lambda$4(ActionContributionItem.java:397)
    at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
    at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4410)
    at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1079)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4228)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3816)
    at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$4.run(PartRenderingEngine.java:1121)
    at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:336)
    at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:1022)
    at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:150)
    at org.eclipse.ui.internal.Workbench$5.run(Workbench.java:687)
    at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:336)
    at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:604)
    at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:148)
    at org.knime.product.rcp.KNIMEApplication.start(KNIMEApplication.java:141)
    at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
    at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:134)
    at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:104)
    at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:388)
    at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:243)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:673)
    at org.eclipse.equinox.launcher.Main.basicRun(Main.java:610)
    at org.eclipse.equinox.launcher.Main.run(Main.java:1519)

 

Do you have any recommendations on how to get driver to work?

Thanks for your assistance.

greg

 

3

Hi Tobias,  I found a way to fix it.  I went into Menu FILE->Install KNIME Extensions   Then clicked on What is Already Installed hyper link on screen.   Then Uninstalled -> Source for KNIME Connectors for Common Databases.

I then went to Preferences->Database->  and clicked Restore Defaults Button.   Then re-Added the UCanAccess drivers per your instruction in initial post.  In addition I use ORACLE JDBC so I added the .jar file for it also.

Everything works well now.

greg

4

My way to fix the same problem i had is to gather all jar files in a common folder including ucanload.jar and add them individually through the prefs databases page.

5

Hello,

Just checking : are you working with 3.5.2 ?

I my case, it's not working anymore. It was working in December with previous Version :-(

I am trying to find a clue

In fact, the ucanaccess driver doesnt show up anymore in the node settings. Same workflow i have used in december.

Thanks for any advise.

 

Kind regards

Bruno

 

 

8

Yes i use 3.5.2 Knime. I noticed that i’ve done a copy of all .jar in a dedicated folder including ucanload.jar, files i’ve listed in the Knime/database prefs. But i’ve also these files (except ucanload.jar) still in /jre/lib/ext, from first tries to solve that issue. With that configuration and the 4.0.3 UcanAccess driver, it works fine. In december like you i used a previous version. Initially the problem was similar : no ucanaccess driver available in operators. In the Knime database prefs page, i declared .jar in the following order :

  • ucanload
  • jackcess-2.1.9
  • commons-lang-2.6
  • commons-logging-1.1.3
  • hsqldb
  • ucanaccess-4.0.3
9

Ucanaccess 4.0.4 is available. The crucial part of the driver installation is a folder name:
UCanAccess. copy jar and lib folders from archive to the folder and after this add folder in
File/Preferences/Databases. It will work.

1 Like
10

I found this forum post specific to Mac’s on this topic:

The intro to this post showed the use of localhost in the server URL for host and port needs. I am struggling with what format should be used in the mac environment. Here is an example of what I have tried:
jdbc:ucanaccess://localhost/Volumes/Group/Database/TestDB.accdb
Is there a specific format this should be in? Do I need a port number?

11

Hello Tobias,
Thank you for the help in getting connected to an Access Database.

I am having a problem where the connection to the database times out.

image
image1240×40 3.86 KB

Since downloading the UCanAccess I have also had problems with high CPU usage/ slow response times. I’m wondering if anyone else had this issue after downloading UCanAccess? KNIME now is so slow to even open the configure window, any suggestions to fix this are greatly appreciated.

Thanks,
Kate

12

Hi Kate,
this might be a problem with the memory setting of the UCanAccess driver.

When dealing with large databases and using the default “memory” setting (i.e., with driver property memory=true), it is recommended that users allocate sufficient memory to the JVM using the -Xms and -Xmx options. Otherwise it will be necessary to set the driver’s “memory” property to “false”:

Connection conn = DriverManager.getConnection(“jdbc:ucanaccess://c:/data/pippo.mdb;memory=false”);

To change this property append ;memory=false to the JDBC URL in the node dialog.
Bye
Tobias

13

Hello everyone,
I’m happy to announce that with KNIME 4.0 we have introduced a new database framework which now comes with a dedicated connector for Microsoft Access databases. So you no longer need to install any drivers but simply drop the node into your workflow and point it to your Microsoft Access database file.
As always we are happy to hear your feedback about the new connector and the new database framework in general.
Bye
Tobias

2 Likes