Exporting MS Access databases to SQLite.

Posted by – Saturday 2015-07-25

Sometimes you are given a database in MS Access (MDB) format. If your operating system is MS Windows and you have a MS Access license, working with this database should not be a issue.

If your operating system is Linux, the odds of working smoothly with such database are not good. The usual recipe for opening a MS Accesss database – actually, a MDB file – in a Linux machine is using LibreOffice Base plus the appropriate database driver. Usually this recipe will not work at the first attempt – and neither after many.

MS Access to SQLite

The alternative explored in this post is exporting the database from MS Access to SQLite. Both database technologies have in common the feature of being serverless. The output of this export process is a SQLite database which, like a MS Access database, is stored in a single file.

Installing the export tool: mdb-sqlite.

The software we have used to export from MS Access to SQLite format is mdb-sqlite, which is released under the New BSD License.

After downloading from Google Code the code – file mdb-sqlite-1.0.2.tar.bz2 – and copying to the directory /usr/local/, we have installed mdb-sqlite in a Debian machine by following the following steps:

$ cd /usr/local/
$ sudo bzip2 -d mdb-sqlite-1.0.2.tar.bz2
$ sudo tar -xf mdb-sqlite-1.0.2.tar
$ sudo rm mdb-sqlite-1.0.2.tar
$ cd ./mdb-sqlite-1.0.2
$ sudo apt-get install ant
$ sudo ant dist

mdb-sqlite is Java based: hence, a Java Virtual Machine (JVM) is needed. Without a JVM, you cannot neither complete the install process nor running mdb-sqlite. The Debian package openjdk-7-jre provides a JVM.

Exporting from MS Access to SQLite.

Once the install process has been completed, the export process is quite easy:

$ java -jar /usr/local/mdb-sqlite-1.0.2/dist/mdb-sqlite.jar 
$HOME/my-database.mdb $HOME/my-database.sqlite3

The first file – $HOME/my-database.mdb – is the MS Access database to be exported to SQLite format and the second one – $HOME/my-database.sqlite3 – is the output file.

The export process might take several minutes, depending on the size of the source database and your computer performance.

0 Comments on Exporting MS Access databases to SQLite.