Download UCanAccess 5.0.1

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:

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

UCanAccess 5.0.0 requires Java 8 or later to run. Older versions require Java 6 or later.

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/pippo.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>net.sf.ucanaccess</groupId>
    <artifactId>ucanaccess</artifactId>
    <version>5.0.1</version>
</dependency>
... and Maven will add the other dependencies for you. If you are manually adding the dependencies to your classpath you will need to add the ucanaccess‑5.0.1.jar file along with all of the jar files in the lib/ folder of UCanAccess‑5.0.1‑bin.zip (details here).

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>3.0.1</version>
</dependency>
Otherwise, check the Jackcess Encrypt website for more information.

Note that the distribution comes with both a number of jUnit test cases (package net.ucanaccess.test) and a simple Java example class (net.ucanaccess.example.Example) which illustrate how UCanAccess may be used.

Adding UCanAccess to your Java project

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

groupId: net.sf.ucanaccess
artifactId: ucanaccess
version: 5.0.1

Otherwise, see this Stack Overflow answer for details on how to configure your Java project.

Establishing a UCanAccess connection

Class.forName("net.ucanaccess.jdbc.UcanaccessDriver"); /* often not required for Java 6 and later (JDBC 4.x) */
Connection conn=DriverManager.getConnection("jdbc:ucanaccess://<mdb or accdb file path>",user, password);
// for example:
Connection conn=DriverManager.getConnection("jdbc:ucanaccess://c:/pippo.mdb;memory=true");

Connection Properties

memory: set if HSQLDB will store its backing database only in memory. Default is memory=true.

lobScale (since UCanAccess 2.0.9.4): to optimize memory occupation when a relevant percent of memory occupation is for OLE (BLOB) data. This may also allow, in many cases, to use the memory=true setting, whilst it was impossible before. It should be set to a value close to the average size of OLE instances. According with the HSQLDB documentation, the values allowed are 1, 2, 4, 8, 16, 32 (the unit is Kb). Default is 2 if memory=true, 32 (HSQLDB default) otherwise.

skipIndexes (since UCanAccess 2.0.9.4): in order to minimize memory occupation, it allows skipping the creation of simple (untied to contraints) indexes. It doesn't have effect on referential integrity constraints (i.e., Index Unique, Foreign Key or Primary Key).

newDatabaseVersion: UCanAccess will create a new Access database file in the specified version if this parameter is set and the file specified in the JDBC connection URL does not exist. Valid values for this parameter are: V2000, V2003, V2007, V2010.

openExclusive (replaces lockMdb which has been deprecated since UCanAccess 2.0.9.4): if openExclusive is enabled (openExclusive=true), UCanAccess locks the mdb/accdb file to prevent updates from other processes. Default=false.

showSchema: if true, catalog "PUBLIC" and schema "PUBLIC" and other HSQLDB schemas are shown in the DatabaseMetadata implementation. Default=false. Using showSchema=true is recommended with Squirrel SQL.

inactivityTimeout (since UcanAccess 0.1.1): it is used only with the default memory mode (memory=true). If there are no active connections for the inactivityTimeout period (in minutes) HSQLDB will be temporarily shut down and any filesystem resources will be released. It will be restarted at the next connection. Setting inactivityTimeout=0 can be used for avoiding this behaviour. Default = 2 minutes.

immediatelyReleaseResources (replaces singleConnection which has been deprecated since UCanAccess 3.0.6): it is used for for ETL jobs, scheduled tasks, or "one-shot" use of UCanAccess with only one connection open. All resources (memory and filesystem) will be released at the closing of the connection. Default=false.

encrypt (since UCanAccess 1.0.4): it allows HSQLDB files encryption. You can use it in conjunction with memory=false. Default=false.

sysSchema (since UCanAccess 1.0.4): if true the Access system tables will be loaded in the "sys" readonly schema. Default=false.

ignoreCase: Disables (ignoreCase=true) or enables (ignoreCase=false) the case sensitivity of text comparison. Default=true.

// for example:
Connection conn=DriverManager.getConnection("jdbc:ucanaccess://c:/pippo.mdb;openExclusive=true;ignoreCase=true");

remap (since UCanAccess-2.0.2): it allows to remap the paths to one or more external linked databases.

The syntax is:

jdbc:ucanaccess://C:/db/<main db>;remap=<original db1 path>|<new db1 path>&<original i-db path>|<new i-db path>

e.g.,

jdbc:ucanaccess://C:/db/main.mdb;remap=c:\db\linkee1.mdb|C:\pluto\linkee1.mdb&c:\db\linkee2.mdb|C:\pluto\linkee2.mdb

Original path and new path must be separated by '|', different pairs must be separated by '&'.

mirrorFolder (since UCanAccess 2.0.9.3): it allows users to set the directory in which the mirror db will be created.
It forces memory=false.
Simply use mirrorFolder=java.io.tmpdir in order to specify the system temp folder for that.

keepMirror (since UCanAccess 2.0.2): keeps the HSQLDB mirror database after the VM ends so it can be reused by subsequent VM processes.
It forces memory=false.
In other words, using this parameter, the time-expensive process of creating and populating the database hsqldb (with memory=false), is executed only once.
jdbc:ucanaccess://C:/db/main.mdb;keepMirror=C:/db/mirrorName
Reusing the same keepMirror setting (e.g. keepMirror=C:/db/mirrorName) at the next VM process execution allows you to dramatically reduce the time of first connection. It should be used only with very large databases (e.g., 1GB with many binary OLE objects) and when:
  • You have to change the default memory=true setting because you can't allocate sufficient JVM heap space (Xmx VM paramenter).
  • UCanAccess takes too much time to establish the first connection (because it's populating the HSQLDB mirror database).
Caution! - If the access database is modified by a different program (so not using UCanAccess) after the HSQLDB creates the mirror database, UCanAccess recreates and repopulates the whole mirror database for avoiding unchecked misalignments.

columnOrder (since UCanAccess 2.0.9): use the "DISPLAY" order of columns with SELECT * statements. ("DATA" order is still the default).
jdbc:ucanaccess://c:/db/cico.mdb;columnOrder=DISPLAY
Note that once a column order has been set on the first connection to a given database, that setting will be the same for all the following connections to that database, in the whole VM life.

concatNulls (since UCanAccess 3.0.0): controls the behaviour of all concatenation operators (&,+,||) with null values:
null & 'thing' -> 'thing'. (It gave null prior to version 3.0.0.)
null + 'thing' -> 'thing'. (It gave null prior to version 3.0.0.)
If you want to switch to the old behaviour, you have to set the new connection property concatNulls=true.

preventReloading (since UCanAccess 3.0.0): optimize the reloading of db instances. If and only if you're sure that your db can be accessed by the MS Access GUI and just one JVM instance (using UCanAccess), you can use the connection parameter preventReloading=true. It avoids the data being reloaded when they aren't actually changed by the GUI.

jackcessOpener (since UCanAccess 0.0.2): in order to use Jackcess Encrypt extension, you can specify a class that implements the net.ucanaccess.jdbc.JackcessOpenerInterface interface (in that case you need additional jar files in your classpath: a version of jackcess-encrypt.jar and all related dependencies).
The following example shows a custom integration with Jackcess Encrypt for opening a Money (.mny) file:

Example for UCanAccess2. Notice that you must use UCanAccess 2.x.x with jackcess-encrypt-2.x.x and all related dependecies
package yourPackage.example;

import java.io.File;
import net.ucanaccess.jdbc.JackcessOpenerInterface;
//imports from Jackcess Encrypt
import com.healthmarketscience.jackcess.CryptCodecProvider;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.DatabaseBuilder;

import net.ucanaccess.jdbc.JackcessOpenerInterface;

public class CryptCodecOpener implements JackcessOpenerInterface {
  public Database open(File fl,String pwd) throws IOException {
   DatabaseBuilder dbd =new DatabaseBuilder(fl);
   dbd.setAutoSync(false);
   dbd.setCodecProvider(new CryptCodecProvider(pwd));
   dbd.setReadOnly(false);
   return dbd.open();
   
  }
  // Notice that the parameter setting AutoSync=false is recommended with UCanAccess for performance reasons.
  // UCanAccess flushes the updates to disk at transaction end.
  // For more details about autosync parameter (and related tradeoff), see the Jackcess documentation.
}


Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
Connection conn=
DriverManager.getConnection("jdbc:ucanaccess:///opt/prova1.mny;jackcessOpener=yourPackage.example.CryptCodecOpener", "sa", pwd);
...

For old UCanAccess versions (0.x.x,1.x.x) JackcessOpenerInterface should be implemented in a different way, because of the jackcess API changes from jackcess1 to jackcess2.
package yourPackage.example;

import java.io.File;
import java.io.IOException;
//imports from Jackcess Encrypt
import com.healthmarketscience.jackcess.CryptCodecProvider;
import com.healthmarketscience.jackcess.Database;

import net.ucanaccess.jdbc.JackcessOpenerInterface;

public class CryptCodecOpener implements JackcessOpenerInterface {
  public Database open(File fl,String pwd) throws IOException {
   return Database.open(fl, true, true, null, null, new CryptCodecProvider(pwd));
  }
}

...

Identifiers escaping

You should escaping identifiers including a blank space using square brackets or backtick character (`).
Also you shold escaping identifiers that equal a SQL keyword (e.g. SELECT * FROM [SELECT] or SELECT * FROM `SELECT`).
SQL Keywords are: ALL, AND,ANY, AS, AT, AVG, BETWEEN, BOTH, BY, CALL, CASE, CAST, COALESCE, CONSTRAINT, CORRESPONDING, CONVERT, COUNT, CREATE, CROSS, DEFAULT, DISTINCT,DO, DROP, ELSE, EVERY, EXISTS, EXCEPT, FOR, FROM, FULL, GRANT, GROUP, HAVING, IN, INNER , INTERSECT, INTO, IS, JOIN, LEFT, LEADING, LIKE, MAX , MIN, NATURAL, NOT, NULLIF, ON, ORDER, OR, OUTER, PRIMARY, REFERENCES, RIGHT, SELECT, SET, SOME, STDDEV_POP, STDDEV_SAMP, SUM, TABLE, THEN, TO, TRAILING, TRIGGER, UNION, UNIQUE, USING, USER, VALUES, VAR_POP, VAR_SAMP, WHEN, WHERE, WITH, END.

Examples

Creating tables with DDL

Statement st =conn.createStatement(); st.execute("CREATE TABLE example1 (id COUNTER PRIMARY KEY,descr text(400), number numeric(12,3), date0 datetime) ");
...st.execute("create table dkey(c counter , number numeric(23,5) , PRIMARY KEY (C,NUMBER) ");
...st.execute("create table dtrx(c text , number numeric(23,5) , unique (C,NUMBER)) ");
...st.execute(" CREATE TABLE Parent( x autoincrement PRIMARY KEY, y text(222))");
...st.execute(" CREATE TABLE Babe( k LONG , y LONG, PRIMARY KEY(k,y), FOREIGN KEY (y) REFERENCES Parent (x) )");
Notice that the part highlighted in red in the statement above is only supported since UCanAccess 4.0.0.

Also UCanAccess supports create table as (<sql select>) syntax:
st.executeUpdate("CREATE TABLE copy as (select * from example1) WITH DATA ");

Executing queries

The following example shows a full outer join:
st =conn.createStatement();
ResultSet rs=st.executeQuery("SELECT * FROM example3 full outer join example4 on (example3.id=example4.id)");

Using Access functions

ResultSet rs=st.executeQuery("SELECT IIf(descr='Show must go off','tizio','caio&sempronio'&'&Marco Amadei'&' '& Now() & RTrim(' I''m proud of you ')) FROM example1");

// Domain Aggregate functions:
ResultSet rs=st.executeQuery("select id, DCount('*','t234','1=1') from t234");

More complete examples of access functions implementation are shown in the net.ucanaccess.test.FunctionsTest junit test suite. The following functions are already implemented:
ASC, ATN, CBOOL, CCUR, CDATE, CDBL, CDEC, CINT, CLONG, CSIGN, CSTR, CVAR, DATEADD, DATEDIFF, DATEPART, DATE, DATESERIAL, DATEVALUE, FIX, FORMAT, IIF, INSTR, INSTRREV, ISDATE, ISNUMERIC, INT, IsNull, LEN, MID, MONTHNAME, NOW, NZ, PARTITION, SIGN, SPACE, SQR, STR,, STRING, STRCOMP, STRCONV, STRREVERSE, SWITCH, RND, TIME, TIMESERIAL, VAL, WEEKDAY, WEEKDAYNAME;
Aggregate and Domain Functions: FIRST, LAST, DCOUNT, DAVG, DSUM, DMAX, DMIN, DFIRST, DLAST, DLOOKUP.
Financial Functions(since UCanAccess 2.0.7.1): PMT, NPER, IPMT, PPMT, RATE, PV, FV, DDB, SYD, SLN.
Also you can use the following functions from the hsqldb implementation:
COS, SIN, LTRIM, RTRIM, UCASE, LCASE;
Aggregate Functions: COUNT, AVG, SUM, MAX, MIN, STDEV, STDEVP, VAR, VARP.

User-defined functions

Step 1: implementation of a new function:
public class XXX{
...
 @FunctionType(functionName="justconcat",argumentTypes={AccessType.TEXT,AccessType.TEXT},returnType=AccessType.TEXT)
  public static String concat(String s1,String s2){
  return s1+" >>>>"+s2;
 } …
}
Step 2: registration:
UcanaccessConnection uc=(UcanaccessConnection)this.ucaConn; uc.addFunctions(XXX.class);
Step 3: trying your new fuction:
ResultSet rs=st.executeQuery("SELECT justConcat(descr,''&now()) from example1");

Using criteria in LIKE clause

ResultSet rs=st.executeQuery("select descr from example2 where descr like 'P%'");
//access * jolly
rs=st.executeQuery("select descr from example2 where descr like 'P*'");
//number and interval patterns
rs=st.executeQuery("select descr from example2 where descr like 'P[A-F]###'");
//number pattern
rs=st.executeQuery("select descr from example2 where descr like 'C#V##'");

Limit and offset SQL pagination

rs=st.executeQuery("SELECT * FROM example2 order by id desc limit 5 offset 1");
rs=st.executeQuery("SELECT TOP 10 * FROM example2 order by id");

Performing transactions

conn.setAutoCommit(false);
st =this.ucaConn.createStatement();
st.executeUpdate("update example4 set descr='Lugo di Romagna'");
st.execute("insert into example4 (ID, descr) values(5,'DALLAS')");
conn.commit();

Using updatable ResultSet

PreparedStatement ps = super.ucanaccess.prepareStatement( "SELECT * FROM T1", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
rs = ps.executeQuery();
rs.next();
rs.updateString(2, "show must go off");
rs.updateRow();

Working with date type

st.execute("INSERT INTO example1 (descr,number,date0) VALUES( \"Show must go up and down\",-113.55446,#11/22/2003 10:42:58 PM#)");

Working with OLE type

InputStream isDB = rs.getBinaryStream('OleColumn');
File fl = new File("CopyElisaArt.JPG");
OutputStream outFile = new FileOutputStream(fl);
ba = new byte[4096];
while ((len = isDB.read(ba)) != -1) {
outFile.write(ba, 0, len); }
out.flush();
out.close();
System.out.println("CopyElisaArt.JPG was created in " + fl.getAbsolutePath());

About ' and " escaping

Both ' and " are valid string delimeters.
So the following SQL statements are correct:
UPDATE TABLE1 SET SURN='D''Ambrogio';(value saved=D'Ambrogio)
UPDATE TABLE1 SET SURN="""D'Ambrogio""";(value saved="D'Ambrogio")

Working with Complex Types

...
PreparedStatement ps=conn.prepareStatement("INSERT INTO TABLE1(ID , MEMO_DATA , APPEND_MEMO_DATA , MULTI_VALUE_DATA , ATTACH_DATA) VALUES (?,?,?,?,?)");
ps.setString(1, "row12");
ps.setString(2,"ciao");
ps.setString(3,"to version");
SingleValue[] svs=new SingleValue[]{new SingleValue("ccc16"),new SingleValue("ccc24") };
//Array of net.ucanaccess.complex.SingleValue

ps.setObject(4,svs);
Attachment[] atcs=new Attachment[]{new Attachment(null,"ccc.txt","txt","ddddd ddd".getBytes(), new Date(),null),
new Attachment(null,"ccczz.txt","txt","ddddd zzddd".getBytes(), new Date(),null) };
//Array of net.ucanaccess.complex.Attachment

ps.setObject(5,atcs);
ps.execute();
...


ps=conn.prepareStatement("UPDATE TABLE1 SET APPEND_MEMO_DATA='THE CAT' ");
//APPEND_MEMO_DATA is an append only column: a new net.ucanaccess.complex.Version will be added to the version array

ps.execute();
...

// insert existing file as an attachment
//   (note that the parameter value is an Attachment *array*, even for a single attachment)
String attachmentPath = "C:/Users/Gord/Documents/sample.pdf";
byte[] attachmentData = java.nio.file.Files.readAllBytes(Paths.get(attachmentPath));
ps=super.ucanaccess.prepareStatement("UPDATE TABLE1 SET ATTACH_DATA=?");
Attachment[] attachmentArray = new Attachment[] {
    new Attachment(null, "sample.pdf", "pdf", attachmentData, new java.util.Date(), null)
};
ps.setObject(1, attachmentArray);
ps.executeUpdate();

//you can't use = operator here: use the Equals or the EqualsIgnoreOrder functions instead
ps=conn.prepareStatement("select * from Table1 where Equals(ATTACH_DATA,?);
ps.setObject(1,atc);
ResultSet rs=ps.executeQuery();
while(rs.next()){
  System.out.println(rs.getObject("ATTACH_DATA"));
//rs.getObject("ATTACH_DATA") will return an array of net.ucanaccess.complex.Attachment
}
...

Filtering on Complex Type Columns (feature available since UCanAccess 2.0.9)

The equality operator (=) can't be used on complex type columns, use the following functions instead.


-Equals. It returns true if the two specified arrays of SingleValue, Attachment or Version are equal to one another, false otherwise.
In other words they are equal if all corresponding pairs of elements in the two arrays are equal.

//true for all records whose MULTI_VALUE_COLUMN value equals [value1,value2]
...
PreparedStatement ps=conn.prepareStatement("select * from TABLE1 WHERE Equals(MULTI_VALUE_COLUMN,?)");
ps.setObject(1, SingleValue.multipleValue("value1","value2",));
ResultSet rs=ps.executeQuery();
...


-EqualsIgnoreOrder. It returns true if the two specified arrays of SingleValue, Attachment or Version are equal to one another regardless the order, false otherwise.

//true for all records whose MULTI_VALUE_COLUMN value equals [value1,value2] or [value2,value1]
...
PreparedStatement ps=conn.prepareStatement("select * from TABLE1 WHERE EqualsIgnoreOrder(MULTI_VALUE_COLUMN,?)");
ps.setObject(1, SingleValue.multipleValue("value1","value2",));
ResultSet rs=ps.executeQuery();
...


-Contains. It returns true if the Array of SingleValue, Attachment or Version(column value) passed as first argument contains
all the element of the Array passed as second argument.

//true for all records whose MULTI_VALUE_COLUMN value contains both value1 and value2
...
PreparedStatement ps=conn.prepareStatement("select * from TABLE1 WHERE Contains(MULTI_VALUE_COLUMN,?) ");
ps.setObject(1, SingleValue.multipleValue("value1","value3"));
ResultSet rs=ps.executeQuery();
...

Table renaming (feature available since UCanAccess 4.0.0)

st.execute("ALTER TABLE [My old name] RENAME TO [My new name]");

Add new Column to existing table (feature available since UCanAccess 4.0.0)

st.execute("ALTER TABLE xxx ADD COLUMN yyy TEXT");
st.execute("ALTER TABLE zzz ADD COLUMN kkk DATETIME NOT NULL DEFAULT now()");
st.execute("ALTER TABLE [222 crazy name] ADD COLUMN [another crazy name] numeric (23,6) default 13.031955 not null");

Add new Index to existing table (feature available since UCanAccess 4.0.0)

st.execute("CREATE unique INDEX [index name] on [table name] (a ASC,c ASC )");
st.execute("CREATE INDEX [index name] on bbb (a ASC,c ASC )");

Add Primary Key to existing table (feature available since UCanAccess 4.0.0)

st.execute("ALTER TABLE xxx ADD PRIMARY KEY (column1,column2)");

Foreign Key creation (feature available since UCanAccess 4.0.0)

st.execute("ALTER TABLE x ADD CONSTRAINT constr FOREIGN KEY (colx1,colx2) REFERENCES y (coly1, coly2) ON DELETE CASCADE"); or
st.execute("ALTER TABLE x ADD CONSTRAINT constr FOREIGN KEY (colx1,colx2) REFERENCES y (coly1, coly2) ON DELETE SET NULL");

Counters handling (feature available since UCanAccess 3.0.0)

st.execute("DISABLE AUTOINCREMENT ON TABLE_NAME");
and
st.execute("ENABLE AUTOINCREMENT ON TABLE_NAME");

Using the UCanAccess command-line console

Execute console.bat (Windows) or console.sh (Linux/unix) in the distribution to run the command-line tool (main class: net.ucanaccess.console.Main). It will prompt you to enter the path to the Access DB file. Once it reads in the database file, it will print out a prompt. You can enter SQL queries or commands at this prompt. All queries and commands end with a semicolon (";").

A SQL query looks like this:

UCanAccess>SELECT * FROM License;

You can export the result of the most recent SQL query to a CSV file using the export command:

UCanAccess>export License.csv;

The export command supports a number of flags, which are described by the --help flag: 

UCanAccess>export --help;
Usage: export [--help] [--bom] [-d <delimiter>] [-t <table>] [--big_query_schema <pathToSchemaFile>] [--newlines] <pathToCsv>;
Export the most recent SQL query to the given <pathToCsv> file.
  -d <delimiter> Set the CSV column delimiter (default: ';').
  -t <table>     Output the <table> instead of the previous query.
  --big_query_schema <schemaFile>  Output the BigQuery schema to <schemaFile>.
  --bom          Output the UTF-8 byte order mark.
  --newlines     Preserve embedded newlines (\r, \n).
  --help         Print this help message.
Single (') or double (") quoted strings are supported.
Backslash (\) escaping (e.g. \n, \t) is enabled within quotes.
Use two backslashes (\\) to insert one backslash within quotes (e.g. "c:\\temp\\newfile.csv").

-d <delimiter> By default the delimiter between columns in the CSV file is the semicolon (;) for historical reasons. If you want to change this to a comma, use this flag like this:

UCanAccess>export -d , License.csv;

-t <table> By default the export command uses the result set of the previous SQL command. This flag sends the given <table> to the CSV file by executing the SQL query SELECT * FROM [table]. For example,

UCanAccess>export -t License License.csv;

-big_query_schema <schemaFile> This flag generates the JSON formatted schema file which contains the data type information (e.g. "string", "integer") of each column which are required by Google BigQuery. This schema file can be used by the BigQuery load tool. For example,

UCanAccess>export -d , -t License --big_query_schema License.schema License.csv;
will generate two files, License.csv and License.schema. These can be imported into Google BigQuery using the bq load command like this:
$ bq load --skip_leading_rows=1 mydataset.License License.csv License.schema

--bom The CSV file will be encoded in UTF-8 format. Microsoft Excel does not support UTF-8 encoding unless the file contains a Byte Order Mark. This flag produces that byte order mark (EF BB BF).

UCanAccess>export --bom -t License License.csv;

--newlines By default any embedded newlines (\n or \r, in other words, ASCII characters 0x0A and 0x0D respectively) are converted into a space character in the CSV file. This is because many CSV readers are not able to read multi-line records. This flag preserves newline characters by enclosing them in double-quote characters.

UCanAccess>export --newlines -t License License.csv;
Microsoft Excel supports newline characters within quotes. Google BigQuery requires the --allow_quoted_newlines flag to be given, like this:
$ bq load --allow_quoted_newlines --skip_leading_rows=1 mydataset.License License.csv License.schema

Quotes The export command supports single or double quotes in the arguments, which allows table names and file names to contain embedded spaces and other special characters. For example, the command

UCanAccess>export -d '\t' -t "License and Address" 'License and Address.csv';
exports the table named "License and Address" to a file named "License and Address.csv", using the TAB character as a delimiter between columns.

Within the quotes, backslash escaping of the following special characters are supported:

  • '\a' - bell (0x07)
  • '\b' - backspace (0x08)
  • '\f' - form feed (0x0C)
  • '\n' - newline (0x0A)
  • '\r' - carriage return (0x0D)
  • '\t' - tab (0x09)
  • '\v' - vertical tab (0x0B)
  • '\\' - backslash (\, 0x5C))
  • '\nn', '\nnn' - octal character escapes
In Windows, the backslash character is the file path separator. If we use quotes, we need to use two backslashes to represent one backslash of a Windows file path. For example the following two commands are identical: 
UCanAccess>export -t License c:\temp\new\License.csv;
UCanAccess>export -t License "c:\\temp\\new\\License.csv";

DBeaver

DBeaver (Universal Database Manager) will automatically download and install UCanAccess as the default MS Access driver.

DBeaver web site: http://dbeaver.jkiss.org

SQLeo

UcanAccess 2.0.2 has been successfully tested with the SQLeo Visual Query Builder.

SQLeo project: http://sourceforge.net/projects/sqleo/

LibreOffice Base / OpenOffice Base

For detailed instructions on how to configure LibreOffice Base or OpenOffice Base with UCanAccess please see this question on Ask Ubuntu.

OpenOffice web site: http://www.openoffice.org
LibreOffice web site: http://www.libreoffice.org/

Using UCanAccess as NetBeans service

Integration tested with UCanAccess 2.0.4.1. It doesn't work with previous UCanAccess versions.

1) Define a new Driver in the Netbeans "Services" tab: Services > Databases > Drivers > New Driver...

2) In the field "Driver File(s)" add "ucanaccess-x.y.z.jar" and all dependencies (all jars in the lib/ folder of the UCanAccess distribution: "commons-lang-2.x.jar", "commons-logging-1.x.y.jar", "hsqldb.jar", "jackcess-2.x.y.jar").

Note: The "commons-logging-1.x.y.jar" version must be the same used by NetBeans (in NetBeans8 the path is "NetBeans 8.0\ide\modules\org-apache-commons-logging.jar"). The commons-logging version in the recent UCanAccess distributions is the same as in NetBeans8 ("commons-logging-1.1.1.jar").

Setting the field Driver Class: net.ucanaccess.jdbc.UcanaccessDriver.

3) Create a connection using the following JDBC Connection URL examples:

Windows local path: jdbc:ucanaccess://C:/Users/Public/Database1.accdb;showSchema=true

Windows UNC path: jdbc:ucanaccess:////servername/sharename/foldername/Database1.accdb;showSchema=true

Linux/unix: jdbc:ucanaccess:///home/gord/Documents/Database1.accdb;showSchema=true
(Notice that the showSchema connection property must be set to true.)

4) Now you can see your db tables listed in the PUBLIC schema.

NetBeans web site: https://netbeans.org

Squirrel SQL

For detailed instructions on how to configure Squirrel SQL with UCanAccess please see this answer on Stack Overflow.

Squirrel SQL web site: http://www.squirrelsql.org/

5.0.1 Release

  • Fix updates failed in tables with Blob/Ole not null
  • Insert/Update of Blob/Ole objects in this simplified way: preparedStatement.setObject(1, new File("c:\\<file path>"));

5.0.0 Release

  • BLOB insert failed for table with multi-column PK
  • Add "Currency" as named format for Format function
  • CREATE TABLE with underscore in table name could cause error
  • Upgrade to hsqldb-2.5.0 and jackcess 3.0.1
  • Support for java.time.LocalTime parameters to prepared statements
  • ParametersTest failures under HSQLDB 2.4.x
  • Date/​Time values corrupted by JVM timezone
  • It now requires Java 8 or later to run

4.0.4 Release

  • Upgrade to Jackcess 2.1.11
  • Support Access_2016 "version 5" file format
  • Initial support for "Large Number" (BIGINT) columns: CRUD, DDL
  • Fix issue with NOT NULL columns created by UCanAccess DDL not respected by ACE/Jet
  • Fix issue with multiple FK constraints between the same two tables
  • Reduce HSQLDB resource consumption by lazy-loading "OLE Object" (BLOB) fields

4.0.3 Release

  • Upgrade to Jackcess 2.1.9
  • Enable arbitrary AutoNumber insert values <= 0
  • Fix CREATE TABLE in UCanAccess (Access unable to open table when last column was declared as AUTOINCREMENT)
  • Respect constraint name when adding foreign key
  • Fix Query failed when Java Locale language is Turkish: uppercasing of column name caused query to fail
  • Fix WHERE clause with NOT LIKE "T#####" caused error
  • Fix Hyphen in DDL column name confused PreparedStatement, e.g., CREATE TABLE zzzFoo ([Req-MTI] TEXT(20))
  • Explicit DDL support for Hyperlink fields, e.g. CREATE TABLE urlTest (id LONG PRIMARY KEY, website HYPERLINK)
  • Maven POM Update, Patch to UcanaccessCallableStatement for Java >= 7 compilers
  • Fix getBlob for null values
  • Fix constraint breach warning referring to wrong row
  • Fix regional settings issue under non-US locales

4.0.2 Release

  • Enhanced the console features
    • Better escaping of exported CSV fields with embedded delimiters and quotes
    • Add -d flag to change delimiter
    • Add -t flag to export large tables directly
    • Add --big_query_schema flag to export the Google BigQuery schema file
    • Add --newlines flag to preserve embedded newlines when exporting to CSV
    • Print UTF-8 byte order mark if --bom flag is given
    • Fix incorrect SimpleDateFormat which outputs 12:00:00 for midnight in the "export" command
  • Optimized the preparser
  • Fix problem with remap= under Linux
  • Fix bug with built-in functions used in calculated field expressions. Added fake left$ and right$ functions
  • Patched mapping of CHAR type when used in DDL
  • Fix insert of arbitrary AutoNumber values
  • Fix bug with the setting of Null values on complex type columns(i.e., Attachment, Multivalue)
  • Fix bug on column aliases containing an accented character (aliases generated by Hibernate)

4.0.1 Release. Fix release

  • Fixed bug that could affect columns order when creating a new table via ALTER TABLE statement and using a particular DDL statements sequence
  • Minor fix on the UCanAccess console

4.0.0 First step towards a full DDL implementation

  • Before this version only limited DDL was supported (CREATE TABLE and DROP TABLE). Now we've implemented:
    • table renaming, e.g.:
      ALTER TABLE [My old name] RENAME TO [My new name]
    • add new Column to existing table, e.g.:
      ALTER TABLE xxx ADD COLUMN yyy TEXT
      ALTER TABLE zzz ADD COLUMN kkk DATETIME NOT NULL DEFAULT now()
      ALTER TABLE [222 crazy name] ADD COLUMN [another crazy name] numeric (23,6) default 13.031955 not null
    • add new Index to existing table, e.g.:
      CREATE unique INDEX [index name] on [table name] (a ASC,c ASC )
      CREATE INDEX [index name] on bbb (a ASC,c ASC )
    • add Primary Key to existing table,, e.g.:
      ALTER TABLE xxx add Primary key (column1,column2)
    • Foreign key creation, e.g.:
      ALTER TABLE x add constraint constr foreign key (colx1,colx2) references y (coly1, coly2) ON delete cascade
      Now you can also create FKs while creating a new table, e.g.,
      CREATE TABLE Babe( k LONG , y LONG, PRIMARY KEY(k,y), FOREIGN KEY (y) REFERENCES Parent (x) )
  • Upgrade to Jackcess 2.1.6

3.0.7

  • Fixed aggregate functions on the datatype DATE (e.g. select first(date_init) from ...)
  • Fixed update queries having a parametric condition in the where clause (e.g. UPDATE COL1 SET COL1=[PAR1] WHERE COL2=[PAR2])
  • Optimized the CLNG function
  • Fixed a few bugs the happened when the parameter keepMirror is being used: see ReloadPersistentMirrorTest, a bug on the ORDER BY case-sensitivity when the access db is updated externally

3.0.6

  • Enhanced DataSource implementation
  • Fixed bug that could have effect when a column name contained both a question mark '?' and a space ' ' characters
  • Fixed bug with APPEND or UPDATE access queries which used a variable of type TEXT as paramenter
  • Fixed naming-conflict bug that could have effect when updating tables with the same name and a different structure from multiple databases connections

3.0.5

  • Fixed major bug on single-precision numeric column management: in particular conditions it could cause misalignment between hsqldb mirror and access db
  • Enhanced DDL(Create Table) compatibility with PreparedStatement for use with other ORM tools
  • Fixed bug on keyword "ROW" (it wasn't listed as keyword)
  • When using the net.ucanaccess.jdbc.UcanloadDriver and the loader.jar, the setting -DUCANACCESS_HOME=D:\301\UCanAccess-3.0.1-bin is no more needed if the loader.jar of your classpath is picked up directly from a standard ucanaccess distribution. (i.e. from the loader folder in the unziped content of the ucanaccess-xxx.bin.zip) Thanks to Gord Thompson for the idea and having suggested the code

3.0.4

  • Implemented MS Access parametric SELECT queries. They will be available, where possibile, using the "table()" syntax, e.g.,
    select * from table(queryWithParameters(#1971-03-13#,'hi babe'))
    A default query, parametrized with all parameters to null, will be available as well.
  • Implemented APPEND, DELETE and UPDATE MS Access queries. They will be available, where possibile, as stored precedures and via CallableStatement, e.g.:
    CallableStatement cs= ucanaccess.prepareCall("{call insert_xxx(?,?,?)}");
  • fixed issue(in the connecting phase) with table/column names starting with the $ character
  • fixed issue(in querying) with table/column names within both a single quote character and a blank space character
  • fixed bug that happened in subsequent drop and create table statements when executed without calling a commit between them
  • fixed dlookup function
  • fixed capitalisation in column metadata for MS Access queries(views) like SELECT tablename.* FROM ....
  • fixed getBestRowIdentifier DatabaseMatadata method
  • the proper exception is thrown when calling executeQuery method for update, insert and delete executions
  • fixed bug on conflicting table names(e.g., my table and my_table)

3.0.3.1

  • Fixed major bug: in several cases exclamation mark in literal textual values (i.e., if you didn't use a PreparedStatement) was saved as point.

3.0.3 Release: still a fix-bugs version, enhancements coming soon

  • fixed bug related to linked tables(from external db) with a complex type column.
  • fixed bug on concurrent access in append on the same table by two or more different processes when using an auto increment key.
  • fixed bug on iif function that now accepts null(interpreted as false) values as first argument.
  • bug on like criteria: now digit intervals are supported ([4-7] or [!2-6]).
  • fixed bug on metadata reloading when a concurrent process does structural upgrades(e.g., when creating a table).
  • fixed bug when connecting to a database with a table/column whose the name contains the reserved character $.
  • fixed bug on # special character escaping in like criteria expressions (to be escaped as usual with [#]).
  • About date values antecedent to the October 15, 1582 (Gregorian):
    • when using a date in literal form (e.g. #1095-05-10#, or passing the string '1095-05-10' to the dateValue function ) this is interpreted in an MS Access compatible way, i.e., pure Gregorian calendar
    • also the FORMAT function is now consistent with this convention
    • when passing to a PreparedStatement a date antecedent to the October 15, 1582, it's an applicative duty to create it consistently within the proper Calendar, i.e. by using GregorianCalendar.setGregorianChange(new java.util.Date(Long.MIN_VALUE)).

3.0.2 Release

  • Fixed bug in CREATE TABLE DDL implementation, when using DECIMAL or NUMERIC columns on nullable(required) column property.

3.0.1 Release

  • Fixed bug in "create table as select..." statement where there is a name conflict in columns defined in the select.
  • Fixed bug in "create table" where one or more column names are the same names of specific access types(e.g., create table ttt(counter counter primary key, text text)).
  • Classloading isolation: in order to avoid conflicts in dependencies jars used by user development environment a new jar named ucanload.jar has been added to this distribution. This is strongly suggested for the use of ucanaccess with LibreOffice/OpenOffice. In other words, this allows you to use in your applications/tools the version of commons-lang, commons-logging, jackcess and hsqldb you need, without any issue or impact on ucanaccess. If you are facing with a library conflict issue, you MUSTN'T add neither ucanaccess.jar nor any of the dependencies jars to your classpath but:
    • unzip the UCanAccess-3.X.X-bin.zip distribution
    • add to your classpath just the ucanload.jar from the "loader" folder of the distribution
    • set the system variable UCANACCESS_HOME so that it points the UCanAccess-3.x.x-bin folder (e.g. -DUCANACCESS_HOME=D:\301\UCanAccess-3.0.1-bin).
    • when required (e.g. with java 6) use the net.ucanaccess.jdbc.UcanloadDriver (e.g., Class.forName("net.ucanaccess.jdbc.UcanloadDriver")) instead of the original one net.ucanaccess.jdbc.UcanaccessDriver.
    See also the updates to the LibreOffice/OpenOffice configuration on the "JDBC Client Tools" tab of this site.

3.0.0 Release

  • Relicense to Apache 2.0!!
  • Upgrade to jackcess 2.1.2.
  • Metadata issues:
    • Solved capitalization issues in both DatabaseMetaData and ResultSetMetaData implementations. They both have been reimplemented for this specific purpose. Now UCanAccess can provide metadata with the correct capitalization(and no more all in upper case).
    • Changed escaping of column and table names containing special characters or out-of-rules(e.g. starting with a number): now, in these cases, you can and must use the names defined in access(obviously in a case insensitive manner).
    • Supported all characterset with metadata (they were supported in data but not always in metadata elements like column or table names)
    • Fixed isAutoIncrement and isCurrency ResultSetMetaData methods.
    • Customized DatabaseMetaData.getColumns so that it can now provide:
      the default column values in the COLUMN_DEF columm
      the correct IS_AUTOINCREMENT informations
      the correct IS_GENERATEDCOLUMN (used for calculated fields)
      the customized column ORIGINAL_TYPE(original type in access)
    • Customized DatabaseMetaData.getClientInfoProperties method that now returns ucanaccess driver available properties.
  • Implemented, for counters handling :
    DISABLE AUTOINCREMENT ON TABLE_NAME
    and
    ENABLE AUTOINCREMENT ON TABLE_NAME
    It may be useful in import/export of data from and to different tables with the same structure, avoiding to break some FK constraint.
  • Optimization issues:
    • Fixed memory leak in MemoryTimer. It had effect in some particular situations (using the single connection mode if you're connecting to multiple db,in the case continous update by a different process, in the case of pooling on the db with intervals greater than the InactivityTimeout).
    • Optimized reloading of db instances. If and only if you're sure that your db can be accessed by the MS Access GUI and just one JVM instance (using UCanAccess), you can now use the connection parameter PreventReloading=true. It avoid the data to be reloaded when they aren't actually changed by the GUI.
  • Changed behaviour:
    • In updatable ResultSet removed the constraint to set all columns before inserting new rows, even if they are null or autoincrement.
    • Extended SELECT @@IDENTITY and Statement.getGeneratedKeys() features to the GUID type.
    • Concat character operators (&,+,||) behaviour with null:
      null & 'thing' -> 'thing'. It gave null in previous versions.
      null + 'thing' -> 'thing'. It gave null in previous versions.
      If you want to switch to the old behaviour, you have to set the new connection property concatnulls=true.

2.0.9.5 Release(A fix-bugs release waiting for the 3.0.0)

  • Upgrade to Jackcess 2.1.0
  • Re-implemented SWITCH function with a different approach
  • Fixed 2.0.9.4 regression related to databases with corrupted metadata(wrong rows number)
  • Changed read-only exception message for Access 97 files
  • Fixed bug on the value returned by the Statement.execute method when a ddl statement(create table) is executed

2.0.9.4 Release

  • Porting to HSQLDB 2.3.1.
  • Fixed bug that may have effect in the case of a naming conflict between a table in the opened database and a linked table in an external database.
  • Allowed connecting to a corrupted database even if data violate an Unique, Not Null or Foreign Key constraint: tables containing bad data are set read only, and a warning is logged.
  • Added parameter Lobscale, to optimize memory occupation when a relevant percent of memory occupation is for Ole(Blob) data. This may also allow, in many cases, to use the memory=true setting, whilst it was impossible before. It should be set to a value close to the average size of Ole instances. According with the HSQLDB documentation, the values allowed are 1,2,4,8,18,32 (the unit is Kb). Default is 2 if memory=true, 32 (hsqldb default) otherwise.
  • Added parameter Skipindexes(default=false), in order to minimize memory occupation. It allows skipping the creation of simple, untied to contraints, indexes. It doesn't have effect on referential integrity constraints (i.e., Index Unique, Foreign Key or Primary Key).
  • Solved bug related to the ß character, when used in column names.
  • Fixed the error message logged when a db link metadata is broken(for metadata corruption).
  • Overloaded NZ function: it can now accept numeric double values as argument.
  • Speeded database loading (first connection time to a given database).
  • Fixed bug that happened with table or column names containing an apostrophe or a quotation mark.
  • Fixed bug in the switch function parsing.
  • Deprecated and substituted parameter lockMdb: the new name for this parameter is openexclusive.
  • Fixed bug on table autoreferential constraint checks (indipendence from storage order).
  • Supported exclamation point, as well as in the Access SQL syntax.

2.0.9.3 Release

  • Upgrade to jackcess 2.0.8
  • Fixed console output(it sometime showed correctly loaded queries in the list of the queries it wasn't able to load)
  • Fixed issues that may happen with few unregistred keywords(e.g. 'cardinality') if used as column name with a default value. Those keywords didn't have any effect on SQL, except for the particular case above.
  • Fixed Connection setSavepoint(String spn) method(setSavepoint only worked fine with no-arguments);
  • Fixed "Create table as select ..." DDL statement when using with a group by clause with two or more columns.
  • Allowed DDL with PreparedStatement(for ORM libraries' use).
  • Implemented ErrorHandler for invalid characters sequences: an Exception will be thrown to identify in which table, column and row the error occured.
  • Fixed ResultSet.getString when called on a numeric decimal column.
  • Fixed bug on logging/shutdown. At the vm end some unneccessary operation was performed in the case of memory=false and singleConnection=true Logs about on-disk database were shown when program terminates (long after the latest connection was closed).
  • Added mirrorFolder connection parameter that forces memory=false and allows users to set the directory in which the mirror db will be created. Simply set this parameter to java.io.tmpdir in order to specify the system temp folder for that.
  • Fixed residual issue (access 2007) creating new tables on NOT NULL property.
  • Added orderJet function to allow compliance with Jet sorting logic that skip hyphen minus '-', en dash '–', em dash '—'
    and in the middle of a string:
    i.e. even if "-" comes before "/"
    and "a-" before "a/"
    and "a- b" before "a/ b"
    unexpectedly, likely to handle compound words "a/b" comes before "a-b"
    WORKAROUND suggested: if you want the same behaviour of Access: select * from table2 order by orderJet( COLUMN1).

2.0.9.2 Release

  • Upgrade to jackcess 2.0.6.
  • Fixed methods getErrorCode and getSQLState in the UCanAccess SQLException implementation (class UcanaccessSQLException). SQL error codes and states are those gotten from hsqldb (you can handle them by using the constants in net.ucanaccess.jdbc.UcanaccessErrorCodes) except for the internal UCanAccess or Jackcess exceptions that always give the error code UcanaccessErrorCodes.UCANACCESS_GENERIC_ERROR.

2.0.9.1 Release

  • Now UCanAccess completely supports calculated fields, even in insert and/or update statements.
  • Speeded the first connection (loading time) when dealing with large databases, especially with the memory=false.Optimized the use of resources(memory, filesystem).
  • Upgrade to jackcess 2.0.5. A later fix from the jackcess trunk to the 2.0.5 release has been included in this distribution. See either the README.txt or the NOTICE.txt files for further details.

2.0.9 Release

  • A major issue, involving some already damaged mdb, has been solved: the data of some table could be partially loaded with the previous UCanAccess versions. A table in an access database has a row count in its metadata(saved the database), and this issue was due to a misalignment between row count in the metadata and the real number of table rows. So, it has been fixed in UCanAccess 2.0.9 by avoiding to rely on this metadata information while loading data. Also, UCanAccess 2.0.9 will raise a warning in these cases, suggesting to repair the mdb file.
  • Strongly improved the UCanAccess behaviour in the case of multiple processes concurrency.
  • Added connection parameter columnOrder to allow to use, in your SQL, the "display" order ("data" order is still the default).
    e.g. jdbc:ucanaccess://c:/db/cico.mdb;COLUMNORDER=DISPLAY.
    Once a column order has been setted on the first connection to a given database, the column order setting will be the same for all the following connections to that database, in the whole VM life.
  • Allowed special characters and blank spaces in DDL. Column and table names, in this specific case, are not more escaped in the persisted table definition on the access file, but just in the mirror hsqldb database.
    e.g. CREATE TABLE [result ccc]( [aa a()] autoincrement PRIMARY KEY, [---bghhaaf b aa] text(222) default 'vvv')
    DROP TABLE [result ccc]
  • Allowed the use of the "autoincrement" keyword as "counter" synonymous in DDL.
  • Implemented @@IDENTITY: SELECT @@IDENTITY query will return the latest generated key in the context of the current connection.
  • Implemented Statement.getGeneratedKeys(): it returns the latest key generated by the statement.
  • Allowed filtering on complex type columns(version, attachment, multi-value).
    You can do that through one of the following functions: Equals, EqualsIgnoreOrder, Contains.
  • Implemented Partition function.
  • Added support to PreparedStatement.setTime

2.0.8 Release

  • Added support to not standard syntax(accepted by MS Access) DELETE * FROM TABLENAME (besides the always supported standard syntax DELETE FROM TABLENAME).
  • Fixed behaviour when the USER SQL keyword is used as column name. You still need to escape it using square brackets, to avoid ambiguity(e.g. SELECT [USER] FROM USERS). Notice that the user keyword, in general, refers to the database user.
  • Fixed bug that could have effect, in the previous versions, on delete and update statements, when a SQL keyword is used as table name.

2.0.7.1 Release

  • Fix bug on byte type management (when the byte value is between 128 and 255).
  • Strongly enhanced implementation of calculated fields (support to power operator ^, return type management, fixed rounding bug in operations involving integer literals and so on). Nevertheless tables with one or more calculated field are still marked as read-only, waiting for an enhanced I/O support. Also notice that calculated fields values are currently "calculated on fly" (that's, for the moment, just a simulation) and they aren't read from the database through jackcess. As result, elaborating the expressions that depend on the Regional Settings(e.g character string parsed to date value), there could be a difference between UCanAccess output and the value saved in the database, if data were inserted with a locale setting and then they are read with another one.
  • Added financial functions (PMT, NPER, IPMT, PPMT, RATE, PV, FV, DDB, SYD, SLN), SQR and FIX function.
  • Treated some more "special characters" (e.g. °) in column and table names.
  • Fix bugs on DATEVALUE function (internationalisation).
  • Overloading of all "date functions", so that they can accept a string (VARCHAR) instead of a date as argument. Nevertheless keep in mind that this kind of calls forces a parsing operation, with a result which depend on the regional settings (locale), so you had better use the # delimiters and thus pass a date value instead of a character string.

2.0.7 Release

  • Strongly enhanced internationalisation support in date formatting.
  • Solved residual bug on boolean type management.
  • Introduced implementation of calculated fields. Tables with one or more calculated field are still marked as read-only, waiting for an enhanced I/O support.
  • Fixed bug on working mode.

2.0.6.3 Release

  • Fix bugs on Format and IsNumeric functions implementation: fixed the internationalization of numeric values, introduced internationalization (related to the current locale ) in the formatting of date values, rounding mode "Round Half Up" for numeric values when a pattern is passed as argument (e.g., format(48.1425,'.###') gives the string 48.143 as result).
  • Fix bug on Int function: now it returns an Integer value (in previous versions it wrongly returned a short).
  • Added ResultSet.deleteRow implementation.

2.0.6.2 Release

  • Fix bugs on DDL implementation (in the previous version a Unique Key was created instead of a Primary Key, issue using a space string " " as default value, multiple columns Primary Key).
  • Driver version read directly from the manifest file (Package.getImplementationVersion()), pom.xml modified to include default implementation entries in the manifest file.

2.0.6.1 Release

  • Fix bug on batch implementation: consecutive SQL update operations on the same record using executeBatch().
  • Fix bug on saving column "required" property,in the case of autonumber type columns.
  • Fix bug on boolean type management (the handling of null values is different between Access and hsqldb).

2.0.6 Release

  • Fix bug on re-authentication with encrypted databases.
  • Fix bug on the size of textual columns with access-97 files (which are read-only). This bug could cause a failure while connecting to access-97 databases. Therefore, successfully tested read-only connection to .eap files.
  • "CONSTRAINTS" listed and handled as hsqldb keyword (and "patched" MAX which had a space at the end).
  • Allowed non-standard SQL operation with date values: select date()+1 from atable (instead of: select date()+1 DAY from atable) gave error before this release.
  • "select table1.sameName, table2.sameName from ..." ambiguity solved by UCanAccess(for compatibility with the access permissive syntax), when detected in access queries.

2.0.5 Release

  • Fix bug on handling "scale" property in the case of numeric columns with dimension "decimal".
  • Improved integration with NetBeans. You can use UCanAccess 2.0.5 with NetBeans8 Reverse Engineer (e.g., "Entity classes from database", "Database Schema")
  • Fix escaping of '?' character in column names.
  • Jackcess upgrade to the 2.0.4.

2.0.4.1 Release

  • Fix bug in ResultSet.insertRow() implementation.
  • Fix bug on DatabaseMetadata.getTables implementation (this patch allows configuring UCanAccess as NetBeans service).
  • Commons-logging upgrade to the 1.1.1.

2.0.4 Release

  • Fix bug in DDL execution(create table) with the latest jet formats.
  • iif function extension for boolean and numeric and date types.
  • "DO" listed and handled as hsqldb keyword.
  • clng function implementation (it was wrongly named clong).

2.0.3 Release

  • Changed UCanAccess behaviour in handling (incorrect) default column values with a size greater than the column size. UCanAccess has now the same behaviour of Access: a data truncation error will be thrown at the real attempt to insert an incorrect value and no more at the accdb/mdb opening (or, in other words, at the first connection).
  • Improved performances in conversions. In the precedent versions, UCanAccess ran slow in some particular case, e.g., sql statement not parametric(not Prepared Statement), with a very long string containing a double quote (") character.
  • Fixed bug in handling column name with both numbers and spaces (e.g. 'bla 1err').
  • Fixed bug related to numbers passed in scientific notation when not using a PreparedStatement.
  • Jackcess upgrade to 2.0.3.

2.0.2 Release

  • Added connection parameter remap. It allows to remap the paths to one or more external linked databases. The syntax is:
    jdbc:ucanaccess://C:/db/<main db>remap=<original db1 path>|<new db1 path>&<original i-db path>|<new i-db path>
    e.g.,
    jdbc:ucanaccess://C:/db/main.mdb;remap=c:\db\linkee1.mdb|C:\pluto\linkee1.mdb&c:\db\linkee2.mdb|C:\pluto\linkee2.mdb.
    Original path and new path must be separated by '|', different pairs must be separated by '&'.
  • Added connection parameter keepMirror for keeping the mirror hsqldb database after the VM ends and so for reusing it in the following VM processes.
    e.g.,
    jdbc:ucanaccess://C:/db/main.mdb;keepMirror=C:/db/mirrorName
    It make sense only with memory=false (so it forces this setting). It should be used with very large databases only (e.g., 1GB with many OLE binary data) and when UCanAccess takes too much time to establish the first connection.
  • Added support for some ISO-8859 non-roman characters (e.g Euro symbol) in column and table names.
  • Added "END" to the keywords list.
  • Fixed minor bugs on DatabaseMetadata. Thanks to the SQLeo team.

2.0.1 Release

  • Added support for persisting the column properties defined in create table statements: default column value and nullability(i.e. required property). Supported both constants and functions as default values
    e.g., CREATE TABLE AAA ( baaaa text PRIMARY KEY,A long default 3 not null, C text(255) not null, d DATETIME default now(), e text default 'bla')
  • Fix method closeOnCompletion of UcanaccessStatement (patch missed in the 2.0.0)

2.0.0 Release

  • jackcess upgrade to version 2.0.1. So UCanAccess 2.0.0 is compliant with jackcess2 API
  • implemented backtick character (`) for escaping identifiers including blank characters(as alternative of using square brackets)
  • fixed major bug on update and delete statements on tables having one or more column names that contain a blank character: it caused a desync between hsqldb and the access file
  • fixed interfacing problem about using UCanAccess from Open Office or Libre Office (the office tools, in some cases, weren't able to open few tables)
  • fixed problem about turning off jackcess logging
  • added META-INF\services\java.sql.Driver file

1.0.4.1 Release

  • Fixed method closeOnCompletion of UcanaccessStatement
  • fixed memory leak in jet loading (it caused OutOfMemoryError during the loading of very large databases even with the connection parameter memory=false)

1.0.4 Release

  • Improved sql conversion so that access keywords (if used as table names, column names, query names) are no more escaped (with an x before the keyword). Old sql code(if hard-coded in your sources) is still supported so you don't need to change anything.
  • memory=false connection parameter allowed even for encrypted databases by encrypting hsqldb files (file system cache) with a random key.
  • fixed bug on yes/no constants conversion to true/false.
  • added "encrypt" connection parameter for allowing hsqldb files encryption. You can use it in conjunction with Memory=false. False by default.
  • added "sysschema" connection parameter. If this connection parameter is true the msaccess system tables will be loaded in the "sys" readonly schema. False by default.
  • jackcess upgrade to version 1.2.14.2

1.0.3 Release. Fix release

  • fixed escaping for access (no standard sql) access keywords referring to data types:COUNTER,CURRENCY,DATETIME,MEMO,OLE, SINGLE,TEXT,YESNO,GUID when they are used as name of column or table or query
  • improved escaping of column, table, query names for allowing the use of '(' and ')' character
  • fixed a 1.0.2 bug in inserting or updating string with new line or carriage return characters: under particular conditions, \n and \r characters were replaced by a blank character
  • in create table ddl statement, text columns default lenght to 255 where not specified (before the explicit setting of length of text columns was mandatory)
  • patched handling of temporary access artefacts (i.e. temporary tables, queries, foreign key and indexes with the name starting with a tilde)
  • jackcess upgrade to version 1.2.14

1.0.2 Release

  • added write support complex types (i.e., array of Version, Attachment, SingleValue)
  • added Lookup domain function
  • added support for dynamic domain function (e.g.SELECT DCount("ID","Table1","ID <=" &ID) AS Sequence, tValue FROM table1)
  • added support for count aggregate function in cross_tab functions(it was missed in the previous version)
  • added switch function
  • added support for "yes"(converted in true) and "no"(converted in false) boolean constants in queries
  • added functions int, strcomp, strconv, strreverse, rnd, timeserial, string, weekday
  • added support for non-standard subquery sintax [SELECT ... FROM ...]. As T
  • patched "create table" implementation for the right size of text columns
  • fixed regular expression support in like conditions
  • jackess upgrade to version 1.2.13

1.0.1 Release

  • crosstab queries implementation(Transform... Pivot...)
  • added support to STDEV, STDEVP, VAR, VARP aggregate functions
  • basic, readonly support to complex types (i.e., array of Version, Attachment, SingleValue)
  • fix handling of datatime values with millisecond precision(so not inserted with standard ms access api)
  • fix handling of hsqldb keywords and ms access reserved words and other problems escaping related

1.0.0 Release

  • improved csv export
  • added support to optional parameters (firstdayofweek,firstweekofyear) in DatePart function:
    DatePart(interval, date [, firstdayofweek] [, firstweekofyear] )
  • added date() function
  • added support to 'yyyy-MM-dd' and 'yyyy-MM-dd hh:mm:ss' date formats
  • extended support for non-standard naming of tables and columns
  • patched problems in the binding of a column default value to a function (during the access file opening)
  • added the singleConnection driver parameter, for etl job, scheduled tasks or "one-shot" use of UCanAccess
  • patched bug in insert/update operations with very long text values (1000+ characters) using Statement interface
  • cleared the resetting of the Connection AUTOCOMMIT property at the end of each transaction
  • patched SQL bug: '_' wildcard character misinterpreted
  • jackess upgrade to version 1.2.12
  • fixed problems with single precision numeric (float) db type

0.1.1 Release(Beta)

  • Memory usage optimisation: added inactivityTimeout driver parameter. Added a specific junit test case for this (net.ucanaccess.test.MemoryTest)
  • Fixed some bugs (ccur function scale, setted jackcess log level to Level.OFF by default)

Sourceforge UCanAccess Home

Sourceforge Project Home

Licensing

UCanAccess is issued on under the GNU Lesser General Public License 2.1. For further information click here.

Starting with the 3.0.0 version, UCanAccess has been relicensed to Apache 2.0. For further information click here

Author

Marco Amadei

Follow me Follow me

 

Current Contributors

Gord Thompson is contributing to the UCanAccess project by offering support on stackoverflow.com, and by effectively suggesting improvement and enhancement opportunities, managing forums, fixing bugs, and adding features.

Markus Spann is contributing to the features of the core UCanAccess engine.

 

Past Contributors

Tom Hallman contributed with bug reports, testing, and useful comments to the release of UCanAccess v1.0.0, while he was involved in the porting from odbc to jdbc of the MIST project.

Rafael Sisto contributed to the release of UCanAccess v1.0.3 by identifying bugs and suggesting patches, in particular regarding escaping of column names and handling of temporary tables.

Marcel Merkel sent a major patch about the JDBC 4.1 feature closeOnCompletion.