How to convert a JET database to SQLite?

Question

I'm a Linux user so an open-source, Linux-friendly solution would be preferable.

Metzger

are you willing to write code? – Charitacharitable

YES! I can write Python and C++ – Metzger

Have you seen: sqlite.org/cvstrac/wiki?p=ConverterTools ? – Cheesy

Answer 1

MDB Tools is a set of open source libraries and utilities to facilitate exporting data from MS Access databases (mdb files) without using the Microsoft DLLs. Thus non Windows OSs can read the data. Or, to put it another way, they are reverse engineering the layout of the MDB file.

Jackcess is a pure Java library for reading from and writing to MS Access databases. It is part of the OpenHMS project from Health Market Science, Inc. . It is not an application. There is no GUI. It's a library, intended for other developers to use to build Java applications.

ACCESSdb is a JavaScript library used to dynamically connect to and query locally available Microsoft Access database files within Internet Explorer.

Both Jackcess and ACCESSdb are much newer than MDB tools, are more active and has write support.

Isochronism

Answer 2

To complement Tony's answer with examples:

This is how I just did a conversion with MDB Tools to sqlite, in Ubuntu 16.04:

sudo apt install mdbtools

# define variables for easier copy/paste of the rest
in="my-jet4-file"
schema="$in-schema.sql"
out="$in.sqlite"

mdb-schema "$in" sqlite > "$schema"
sqlite3 "$out"          < "$schema"
mdb-tables -1 "$in" \
| while read table; do \
    mdb-export -I sqlite "$in" "$table" | sqlite3 "$out"; \
  done

This uses Insert statements and is quite slow.

A faster alternative would be to export/import .csv files. I had used that sucessfully with Postgres:

#...
out="my_pg_db"

createdb "$out"
mdb-schema "$in" postgres   > "$schema"
psql -U postgres -d "$out" -f "$schema"
mdb-tables -1 "$in" \
| while read table; do \
    mdb-export -d'|' "$in" "$table" > "$table.csv"; \
    psql -d "$out" -c "COPY \"$table\" FROM '$table.csv' DELIMITER '|' CSV HEADER"
  done

Finally, there is also mdb-sqlite, which uses Jackcess and Java. After installing Java and ant:

cd mdb-sqlite-1.0.2
ant dist
java -jar dist/mdb-sqlite.jar "$in" "$out"

Perforce

Answer 3

This is probably not the answer you want but the safest way to do this would be to get Visual Studio Express and read in the database using ODBC connector and then writing out the data using the ADO.NET Sqlite connector. I have found generally third party tools to talk to JET databases... JET waas aweful and never easily reverse engineered.

Nitwit

ODBC is relatively slow (that's an understatement) – Shenyang