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?
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
JO
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.
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
JO
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.
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
1 Like
You cannot mix 32bit and 64bit code in one application.
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
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... :-)
E
There is also the R package Hmisc, which may be of interest...
http://svitsrv25.epfl.ch/R-doc/library/Hmisc/html/mdb.get.html
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
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*
Hi all,
I´m trying write a data in a MS ACCESS table, but when I execute the node I receive this message:
ERROR Database Writer 0:677 Execute failed: UCAExc:::3.0.5 unexpected token: Storecode R
Field Knime Double = ACCESS
Does any one knows how to fix it?
Thank you!
When I export text data everything works very well, but when I try export value data, example total of sales with the format double precision ,the Knime generated this error : ERROR Database Writer 0:681 Execute failed: UCAExc:::3.0.5 unexpected token: Total of sales
Do you know how can I fix it?
Thank you once again!
William Paiva
Hi Everyone,
here https://tech.knime.org/forum/knime-general/no-connection-to-ms-access-database
is a very nice description of the connection.
Best, Iris
Nice Iris, many thanks!
E
Hi im new in knime, and im having a lot of problems reading an ms access table in knim 3.2 i wanna know if theres a simple route with the database reader node or some other node becouse i dont understand how the database driver works, i was runing in 3.1.7 ucanload and i does work (sorry not english native)
The UCanAccess driver is very unstable in KNIME, in my experience. You can leverage the same lower-level library (jackcess) to implement a custom Access database reader (in a Java Snippet). You will need to add the following (3) external .jar files (that are packaged with UCanAccess) to the Java Snippet node: jackcess*.jar, common*.jar
The Java Snippet takes input table with 1 row and 4 columns; file, table, match_on, match_vals
- match_vals is a comma delimited entry
- leave match_on and match_vals empty to return the entire table
The output is a list column that can be parsed using native KNIME nodes (i.e. Ungroup)
// system imports
import org.knime.base.node.jsnippet.expression.AbstractJSnippet;
import org.knime.base.node.jsnippet.expression.Abort;
import org.knime.base.node.jsnippet.expression.Cell;
import org.knime.base.node.jsnippet.expression.ColumnException;
import org.knime.base.node.jsnippet.expression.TypeException;
import static org.knime.base.node.jsnippet.expression.Type.*;
import java.util.Date;
import java.util.Calendar;
import org.w3c.dom.Document;
// Your custom imports:
import org.knime.core.node.NodeLogger;
import com.healthmarketscience.jackcess.*;
import com.healthmarketscience.jackcess.impl.*;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Arrays;
// system variables
public class JSnippet extends AbstractJSnippet {
// Fields for input columns
/** Input column: "file" */
public String c_file;
/** Input column: "table" */
public String c_table;
/** Input column: "match_vals" */
public String c_match_vals;
/** Input column: "match_on" */
public String c_match_on;
// Fields for output columns
/** Output column: "records" */
public String[] out_records;
// Your custom variables:
NodeLogger logger = NodeLogger.getLogger(AbstractJSnippet.class);
// expression start
public void snippet() throws TypeException, ColumnException, Abort {
// Enter your code here:
try {
File db_file = new File(c_file);
Database db = DatabaseBuilder.open(db_file);
Table table = db.getTable(c_table);
List<String> records = new ArrayList<String>();
List<String> match_vals = (c_match_vals != null) ? Arrays.asList(c_match_vals.replaceAll(" ","").split(",")) : new ArrayList<String>();
Integer cnt=0;
String mv;
for(Row row : table){
mv = null;
if (c_match_on != null) {
if (row.get(c_match_on) == null) continue;
if (row.get(c_match_on) instanceof Double) {
Integer mv_int = (int) Double.parseDouble(row.get(c_match_on).toString());
mv = mv_int.toString();
} else {
mv = row.get(c_match_on).toString();
}
if (match_vals.contains(mv)) {
records.add(row.toString().split("\\[\\{")[1].replaceAll("\\}\\]",""));
}
} else {
records.add(row.toString().split("\\[\\{")[1].replaceAll("\\}\\]",""));
}
cnt = cnt+1;
//if (cnt>9) break;
}
db.close();
out_records = records.toArray(new String[0]);
//logger.warn(records);
} catch (IOException e) {
logger.error("ERROR : "+e.getMessage());
}
// expression end
}
}
1 Like