There is a new KNIME forum. You can still browse and read content from our old forum but if you want to create new posts or join ongoing discussions, please visit our new KNIME forum: https://forum.knime.com

How to connect to MS Access Database

Member for

11 years 2 months tobias.koetter

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

 

 

Comments
Tue, 01/09/2018 - 07:42

Member for

4 years 4 months

gwitknime

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

 

Fri, 01/12/2018 - 12:03

Member for

4 years 4 months

gwitknime

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

Mon, 02/26/2018 - 08:51

Member for

1 year 10 months

Corsaire50

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.

Fri, 03/02/2018 - 03:15

Member for

2 years 5 months

bruno.grall@epershand.eu

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

 

 

Files