UCanAccess

UCanAccess

This is the home page of UCanAccess, an open-source Java JDBC driver implementation that allows Java developers and JDBC client programs (e.g., DBeaver, NetBeans, SQLeo, OpenOffice Base, LibreOffice Base, Squirrel SQL) to read/write Microsoft Access databases.

Because it is a pure Java implementation it runs on both Windows and non-Windows operating systems (e.g., Linux/Unix). No ODBC needed. UCanAccess uses:

• Jackcess as MS Access input/output library (Web site: http://jackcess.sourceforge.net/)
• HSQLDB (aka HyperSQL) as synchronized DBMS (Web site: http://hsqldb.org/)

Features

• Supported Access formats: 2000, 2002/2003, 2007, 2010/2013/2016 databases. (Access 97 format supported for read-only)
• 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 application server threads.
• Connection pooling.
• ANSI 92 SQL, core SQL-2008.
• Access SQL.
• Core built-in functions for Access SQL are supported (e.g., Date(), Now(), IIf(), ...).
• You can execute Select queries defined and saved in Access. Since version 1.0.1, UCanAccess can execute Crosstab queries (TRANSFORM ... PIVOT ...)
• LIKE operator accepts both standard % and Access-specific * as multi-character wildcards, both standard _ and Access-specific ? as single-character wildcards, # for single digit and [xxx][!xxx] for character intervals (Access).
• Both & and + (Access SQL) as well as || (ANSI SQL) for string concatenation.
• Square brackets for field names, table names and view names (saved Access queries) that include spaces or other special characters, e.g., SELECT [my column] FROM [my table].
• Access date format (e.g., #11/22/2003 10:42:58 PM#).
• Both double quote " and single quote ' as SQL string delimiters.
• Read and write support to complex types (i.e., array of Version, Attachment, SingleValue).
• Since version 2.0.1, UCanAccess supports both constants and functions as default column values in CREATE TABLE statements e.g. CREATE TABLE tbl (fld1 TEXT PRIMARY KEY, fld2 LONG DEFAULT 3 NOT NULL, fld3 TEXT(255) NOT NULL, fld4 DATETIME DEFAULT Now(), fld5 TEXT DEFAULT 'bla')
• Command-line console ("console.bat" and "console.sh"). You can run SQL commands and display their results. CSV export command included.

Limitations

• Access data macros are not supported. UCanAccess can update tables for which event-driven data macros are defined, but the macros themselves are ignored.
• In versions prior to UCanAccess 4.0.0, only basic DDL was supported (CREATE TABLE with Primary Key, DROP TABLE).
  Since version 4.0.0 UCanAccess can now support DDL for Table metadata modification:
  • Columns, Primary Keys and other Indexes can now be added to an existing table.
  • UCanAccess 4.x.x has also the ability to create Foreign Keys and to rename Tables.
• Very limited multiprocess access support (details here). Nevertheless, it tries to synchronize data when a concurrent process modifies the Access database file.

Requirements

From version 5.1.0 UCanAccess requires Java 11 or later to run.
From version 5.0.0 Java 8 or later is required, while older versions require at least Java 6.

When dealing with large databases and using the default "memory" setting (i.e., with driver property memory=true), it is recommended that users allocate sufficient memory to the JVM using the -Xms and -Xmx options.
Otherwise it will be necessary to set the driver's "memory" property to "false":

Connection conn = DriverManager.getConnection("jdbc:ucanaccess://c:/data/myaccessdb.mdb;memory=false");

Dependencies

UCanAccess depends on Jackcess and HSQLDB. If you are using Apache Maven to manage your dependencies (recommended) simply add the following to your pom.xml:

<dependency>
    <groupId>io.github.spannm</groupId>
    <artifactId>ucanaccess</artifactId>
    <version>5.1.0</version>
</dependency>

Maven will add the other dependencies for you. If you are manually managing your classpath, you will need to add the ucanaccess-5.1.0.jar file along with its dependencies to your classpath.

In order to open encrypted files you must use UCanAccess 2.x.x or later with jackcess-encrypt-2.x.x and all related dependencies. Again, if you are using Maven, simply add this extra dependency:

<dependency>
    <groupId>com.healthmarketscience.jackcess</groupId>
    <artifactId>jackcess-encrypt</artifactId>
    <version>4.0.2</version>
</dependency>