Description

UCanAccess is a pure Java JDBC Driver implementation which allows java developers and jdbc client programs to read/write Microsoft Access database (.mdb and .accdb files). No ODBC needed.

UCanAccess Web Site

License

GNU Library or Lesser General Public License version 2.0 (LGPLv2), Apache License V2.0

Features

• Supported Access formats: 2000, 2003, 2007, 2010.
• SELECT, INSERT, UPDATE, DELETE statements. Transactions and savepoints.
• Access data types: YESNO, BYTE, INTEGER, LONG, SINGLE, DOUBLE, NUMERIC, CURRENCY, COUNTER, TEXT, OLE, MEMO, GUID, DATETIME.
• Concurrent access from multiple users (i.e., multiple application server threads).
• Connection pooling.
• ANSI 92 SQL, core SQL-2008.
• MS Access SQL.
• Main Access functions implementation.
• You can execute select queries defined and saved in Access.
• Criteria in LIKE clause.
• Access date format (e.g., #11/22/2003 10:42:58 PM#).
• Both double quote " and single quote ' as SQL string delimeters.
• Command Line Console. You can run SQL commands and display their results. CSV export command.

Manipulating an Access database from Java without ODBC

I want to manipulate a Microsoft Access database (.accdb or .mdb file) from my Java project. I don't want to use the JDBC-ODBC Bridge and the Access ODBC driver from Microsoft because:

• the JDBC-ODBC Bridge has been removed from Java SE 8 and is not supported (ref: here),
• the JDBC-ODBC Bridge does not work properly with the Access ODBC driver when text includes Unicode characters with code points above U+00FF (ref: here), so such a setup would not be able to handle characters such as Greek, Russian, Chinese, Arabic, etc.,
• the Access ODBC driver from Microsoft only works in Windows, and
• there are separate 32-bit and 64-bit versions of the Access Database Engine (and ODBC driver) which can be a nuisance for deployment.

I have seen other answers mentioning a JDBC driver for Access databases named UCanAccess. How can I set up my Java project to use this approach?

(Answers suggesting better ways of working with Access databases from Java would also be most welcome.)

java eclipse ms-access netbeans-7 ucanaccess

add a comment

UCanAccess is a pure Java JDBC driver that allows us to read from and write to Access databases without using ODBC. It uses two other packages, Jackcess and HSQLDB, to perform these tasks. The following is a brief overview of how to get it set up.

Option 1: Using Maven

If your project uses Maven you can simply include UCanAccess via the following coordinates:

groupId: net.sf.ucanaccess

artifactId: ucanaccess

Option 2: Manually adding the JARs to your project

As mentioned above, UCanAccess requires Jackcess and HSQLDB. Jackcess in turn has its own dependencies. So to use UCanAccess you will need to include the following components:

UCanAccess (ucanaccess-x.x.x.jar)

HSQLDB (hsqldb.jar, version 2.2.5 or newer)

Jackcess (jackcess-2.x.x.jar)

commons-lang (commons-lang-2.6.jar, or newer 2.x version)

commons-logging (commons-logging-1.1.1.jar, or newer 1.x version)

Fortunately, UCanAccess includes all of the required JAR files in its distribution file. When you unzip it you will see something like

ucanaccess-3.0.3.jar     /lib/     commons-lang-2.6.jar       commons-logging-1.1.1.jar       hsqldb.jar       jackcess-2.1.3.jar

All you need to do is add all five (5) JARs to your project.

Eclipse: Right-click the project in Package Explorer and choose Build Path > Configure Build Path.... Click the "Add External JARs..." button to add each of the five (5) JARs. When you are finished your Java Build Path should look something like this

NetBeans: Expand the tree view for your project, right-click the "Libraries" folder and choose "Add JAR/Folder...", then browse to the JAR file.

After adding all five (5) JAR files the "Libraries" folder should look something like this:

IntelliJ IDEA: Choose File > Project Structure... from the main menu. In the "Libraries" pane click the "Add" (+) button and add the five (5) JAR files. Once that is done the project should look something like this:

That's it!

Now "U Can Access" data in .accdb and .mdb files using code like this

// assumes...//     import java.sql.*;Connection conn=DriverManager.getConnection(         "jdbc:ucanaccess://C:/__tmp/test/zzz.accdb");Statement s = conn.createStatement();ResultSet rs = s.executeQuery("SELECT [LastName] FROM [Clients]");while (rs.next()) {     System.out.println(rs.getString(1));}