Built by Maven

JDBC Client Tools

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: https://dbeaver.io/

SQLeo

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

SQLeo project: https://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: "hsqldb.jar", "jackcess-x.y.z.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/