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:
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.)
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.
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>
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. TheUcanloadDriver
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:
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));
}
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.