Manipulating an Access database from Java without ODBC

Question

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

The following is an excerpt from pom.xml, you may need to update the <version> to get the most recent release:

  <dependencies>
    <dependency>
        <groupId>net.sf.ucanaccess</groupId>
        <artifactId>ucanaccess</artifactId>
        <version>4.0.4</version>
    </dependency>
  </dependencies>

 

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-4.0.1.jar  
  /lib/
    commons-lang-2.6.jar  
    commons-logging-1.1.1.jar  
    hsqldb.jar  
    jackcess-2.1.6.jar

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

NOTE: Do not add loader/ucanload.jar to your build path if you are adding the other five (5) JAR files. The UcanloadDriver class is only used in special circumstances and requires a different setup. See the related answer here for details.

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));
}

 

Disclosure

At the time of writing this Q&A I had no involvement in or affiliation with the UCanAccess project; I just used it. I have since become a contributor to the project.