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

Note: If you're working with especially large files or in memory-constrained environments, and performance with memory=false is not acceptable, consider using the Jackcess library directly for low-level access. This bypasses the in-memory HSQLDB layer and can significantly reduce memory usage while improving performance.

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.3</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.2.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>