KNIMEtech | Reading Microsoft Access Database

Reading Microsoft Access Database

23 posts / 0 new

Mon, 02/15/2010 - 22:13

lyle

Offline

Joined: 02/15/2010

Reading Microsoft Access Database

I'm new to Knime and am having trouble opening a MS Access Database file of type "mdb". I'm using the Database Reader component. The Database driver is the default -- "sun.jdbc.odbc.JdbcOdbcDriver". I cannot seem to create a Database URL that works. I've tried the pathname to the target file. Also, I have tried things like jdbc:odbc:. I always get an error indicating that either the driver won't accept the url or a "datasource name too long" message.

Surely this is a simple thing to do -- I do it in java code all of the time:) Can anybody help me get on track?

This is a windows 7 machine.

Thanks in advance,
Lyle

Mon, 02/15/2010 - 22:28

gabriel

Offline

Joined: 01/26/2007

Hi Lyle,
the comment in this thread will guide you how to read data from a Microsoft Access Database.
Best, Thomas

Mon, 02/15/2010 - 22:28

gabriel

Offline

Joined: 01/26/2007

Hi Lyle,
the comment in this thread will guide you how to read data from a Microsoft Access Database.
Best, Thomas

Tue, 02/16/2010 - 16:00

lyle

Offline

Joined: 02/15/2010

Thanks Thomas, this is very helpful.
Lyle

Tue, 12/07/2010 - 18:41

Barry Chen

Offline

Joined: 08/31/2010

Hi Thomas,

The thread doesn't seem to work any more. I have a similar need and would like to see how it is done.

Barry

Wed, 12/08/2010 - 11:06

gabriel

Offline

Joined: 01/26/2007

Hi Barry,

Sorry for the dead link; my original post is attached here:

In order to access a MS Access data source, you need to register your database with the ODBC Data Source Administrator, see http://msdn2.microsoft.com/en-us/library/ms188691.aspx for details. During the configuration of your MS Access data source, you will be asked to define a Data Source Name. This unique ID is then used within KNIME's Database Reader dialog to access the MS Access database. Select the standard JDBC-ODBC driver and enter the URL to your registered the database as jdbc:odbc:YOUR_ID, enter user name and password, if specified, and edit the SQL statement as a last step.

Alternatively, you can use the following syntax to access the MS Access database directly: jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\Database.accdb

Regards, Thomas

Wed, 05/30/2012 - 15:16

(Reply to #6) #7

pro68

Offline

Joined: 05/30/2012

Dear Thomas,

Sorry I am new in Knime I do not understand what is meant by: Use the following syntax .... Where do I use it? I tried to add the driver somewhere in the preferences, but similarly to the discussion about MS SQL drivers I did not find a .jar driver for MS Access neihter. Would be very nice if you could explain option 2 a bit more in detail. Knime 32 bit would be fine.

Thank you in advance

Wed, 05/30/2012 - 16:50

(Reply to #10) #8

gabriel

Offline

Joined: 01/26/2007

If you use Microsoft Access there is no need to register additional driver in the KNIME preferences. You simply use the Database Reader or Database Connector node with the default settings provided and enter the URL as jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\Database.accdb pointing to the Access database that you want to read in. Furthermore, in the SQL Statement (last dialog option) you need to replace the table place holder with the table that you want to read. That's all. Hope it helps?

Thu, 05/31/2012 - 09:04

(Reply to #11) #9

pro68

Offline

Joined: 05/30/2012

Thank you very much,

this really helped. Small note: I had to take the *.accdb out since I do only have MS Access 2003 installed. I am wondering if relative (dynamic) pathes work (e.g. when I want to distribute the workflow) and how to set the home directory.

Thanks again

Thu, 05/31/2012 - 11:01

(Reply to #12) #10

gabriel

Offline

Joined: 01/26/2007

I see two possibilities to have such generic workflow: either you create a ODBC/JDBC system resource and link to the database; the URL in the KNIME Database Reader is then simply jdbc:obdc:yourID or you make use of KNIME's flow variable concept that allows to inject parameters into any node. In order to get the home directory, one could use the Extract System Properties node and filter by user.home using the Row Filter; then you need to append the database file name as well as the protocol, for example with the String Manipulator, translate the single row into a variable using the TableRow to Variable node and inject this into the Database Reader node (enable flow variables ports from the node context menu). Last step, configure the node as usual in the dialog and switch over to the Flow Variables tab, in the URL-property combobox choose the variable that you have just created. The node will then replace this setting always with the variable value during execution time. I know it's tricky, but that a very generic way of parameterizing KNIME workflows which get even more powerful when embedded into loops.

Fri, 06/01/2012 - 13:13

(Reply to #13) #11

pro68

Offline

Joined: 05/30/2012

Hi Gabriel,

thank you for your quick and comprehensive reply. It looks complex but very promising. Especially in case I want to share my workflows with other persons.

Thanks again

Fri, 06/01/2012 - 14:50

(Reply to #14) #12

gabriel

Offline

Joined: 01/26/2007

One last comment along this line: KNIME.com provided commerical components that allow sharing workflows, meta nodes and other data resources very natural through the so-called KNIME TeamSpace (for smaller groups) or KNIME Server. For further question, don't hesitate to contact me directly.

Wed, 12/08/2010 - 22:49

#13

Barry Chen

Offline

Joined: 08/31/2010

I tried the second approach and it worked. Thank you!!

Thu, 04/26/2012 - 17:50

#14

kannan

Offline

Joined: 04/26/2012

Hi Thomas,

I am using a Win7 (64-bit) OS and am trying to connect to a MS Access 2007 database.

I tried both the methods to connect to a access 2007 database. If I am using 32-bit KNIME, then both the methods successfully connects to access databases.

But when using 64-bit KNIME, the same model that worked under 32-bit KNIME, fails to connect to .accdb. The error messages slightly differ depending on the method used:
1) method1 connecting via ODBC DSN produces this error- "ERROR Database Reader Execute failed: java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"
2) method2 connecting directly produces this error: "ERROR Database Reader Execute failed: java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application"

Is it possible to connect to .accdb from 64-bit KNIME?

Thank you,
kannan

Sun, 04/29/2012 - 14:31

#15

gabriel

Offline

Joined: 01/26/2007

I encountered the same problem, but I don't have solution yet. Sorry, the driver seems to work only with 32bit.

Wed, 02/06/2013 - 17:47

#16

Ergonomist

Offline

Joined: 06/18/2008

Dear Thomas,

any solution for this yet? Maybe a dirty hack of integrating parts of 32 bit Eclipse into a 64 bit "query nodes module" or somesuch? :-)

Thanks,
E

Wed, 03/13/2013 - 10:12

#17

thor

Offline

Joined: 02/12/2007

You cannot mix 32bit and 64bit code in one application.

Wed, 03/13/2013 - 16:13

#18

Ergonomist

Offline

Joined: 06/18/2008

Thanks Thorsten,

But maybe it might be possible to call a remote package whose results you parse back in, along the lines of the current R implementation for local execution? Googling brought up the following:

"SQLShell, a cross-platform, cross-database SQL command tool"
http://software.clapper.org/sqlshell/

More Access-specific, this call should work:

"<path>\msaccess.exe" "<path>\MyDatabase.mdb" /x MacroName

with the (VBA) macro generated by wrapping the query as described here:
http://www.tek-tips.com/viewthread.cfm?qid=220430

It's ugly implementation-wise (SQL wrapped in VBA executed by masaccess.exe), but it should work. And automating this in an MS Access node would be absolute beauty for users after all, beast below or not! ;-)

Cheers,
E

Wed, 03/13/2013 - 18:33

#19

thor

Offline

Joined: 02/12/2007

This may be possible but a nightmare to use and program since you are completely lossing information on datatypes, database/table meta-information etc.

Mon, 03/18/2013 - 14:54

#20

Ergonomist

Offline

Joined: 06/18/2008

I agree it's no more than a glorified file reader, but it does have automation capabilities which otherwise don't exist. In fact, if XML were used (instead of CSV) the metadata might make it across after all... :-)

E

Fri, 03/22/2013 - 10:45

#21

Aaron Hart

Offline

Joined: 06/05/2012

There is also the R package Hmisc, which may be of interest...
http://svitsrv25.epfl.ch/R-doc/library/Hmisc/html/mdb.get.html

Wed, 03/27/2013 - 14:09

#22

Ergonomist

Offline

Joined: 06/18/2008

Excellent find, thanks Aaron!

I've taken the search a little further from here and have come up with a tool of potentially even greater interest - the "Jackcess" Java library. According to their FAQ it "[...] supports Access database versions 2000-2010 read/write and Access 97 read-only".

Unfortunately (for me) it uses Java syntax instead of SQL, but hey - can't have it all. Someone with actual coding skills beyond basic snippet creation interested in setting something up? :-)

Cheers
E

Wed, 03/27/2013 - 14:34

#23

Ergonomist

Offline

Joined: 06/18/2008

JDBC for MS Access - it does exist!

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

Was slightly hidden on the MDB Tools Wikipedia page...

Cheers
E

P.S.: Dependencies - could you envisage bundling these with future KNIME distros? Would make it less fiddly...
UCanAccess requires (at least) the following dependencies in your classpath:

jackcess-1.2.9.jar
- commons-lang-2.4.jar
- commons-logging-1.0.4.jar
hsqldb.jar(2.2.x)

P.P.S.: In fact I'd love to see KNIME extensions consolidating open source (and even otherwise distributable) JDBCs - thoughts? I can provide lists... :)

P.P.P.S.: *cough* http://rapid-i.com/wiki/index.php?title=JDBC_Drivers *cough*

Main menu

Search

User login

Reading Microsoft Access Database