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?
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
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?
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.
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?
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.
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.
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.
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.
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"
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! ;-)
This may be possible but a nightmare to use and program since you are completely lossing information on datatypes, database/table meta-information etc.
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... :-)