Database MS Access via UCanAccess not working/loaded

Hi,
I was very happy to find out that one can use MS Access databases without the MS Office 32bit/64bit problem but could not get it working, that is to say I cannot select the UCanAccess driver in the Database Connector node (see attached file

).
Here is what I tried:

  1. Download UCanAccess-4.0.3-bin.zip (from https://sourceforge.net/projects/ucanaccess/files/) and copy commons-lang-x.jar, commons-logging-x.jar, hsqldb.jar, jackcess-x.jar and ucanaccess-x.jar to a local dir and select these files in the “FIle->Preferences->KNIME->Databases” and add them (see attached file knime_db_jar).
    Each of the 5 files is listed and there is no error in the log.
  2. I also did 1 but added the whole directory.
  3. I tried an older version UCanAccess-3.0.7-bin.zip and did the same as in 1.
  4. I regularly restarted KNIME after adding or deleting the jar steps. I also created new workflows and added new Databases Connector nodes.

Circumventing UCanAccess, leverage lower-level libraries directly.

hi @spider,

I encountered the same problem with your’s. How did you fix this?
Thanks in advance.

Regards,
Gambit

Hi there @Gambit,

Which KNIME version and node are you using?

With KNIME 4.0 version there is a dedicated connector for Microsoft Access databases which does not require installing any driver.

Br,
Ivan

Hi @ipazin,

My Knime is version 4.0.1. Can I use Microsoft Access Database Node even though I have no MS office? Currently, I am using Database Connector (Legacy) Node.

Thanks,
Gambit

Hi there,

if you have Microsoft Access Database then you can use Microsoft Access Connector node to connect to it.

Br,
Ivan

Hi @ipazin

I used the new Microsoft Access Connector but the nodes I want to use were only compatible on the legacy type nodes so I think I need to do the old procedure in connecting with .mdb files. Or if you can give other options on how to extract .mdb files and identify if a table or tables exist or not on that specific .mdb file, it would be a great help.

Thanks,
Gambit

Hi there @Gambit,

hmm… Which are the nodes you use and are only compatible with legacy db framework? Each database node from legacy framework should have successor in new database framework.

To identify if table exists you use DB Table Selector with Custom Query option. For SQLite database this is query that will return 0, if the table doesn’t exist, 1 if it does.

SELECT count(*) FROM sqlite_master WHERE type='table' AND name='table_name';

Now you can enrich query to add your logic of inserting data for example if table exists or build your logic with additional nodes to perform necessary action :wink:

Seen you asked it in a separate topic so closing this one.

Br,
Ivan