Geek Answers Handbook

How to convert a JET database to SQLite?

I am a Linux user, so an Linux compatible open source solution is preferred.

+7
linux database jet
source share
3 answers

MDB Tools is a set of open source libraries and utilities to facilitate the export of data from MS Access databases (mdb files) without using the Microsoft DLL. Thus, the OS cannot read data. Or, to put it another way, they reverse the installation of the layout of the MDB file.

Jackcess is a pure Java library for reading and writing to MS Access databases. This is part of the OpenHMS project from Health Market Science, Inc .. This is not an application. There is no graphical interface. This is a library designed for other developers to create Java applications.

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

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

+7
source share

This is probably not the answer you need, but the safest way to do this is to get Visual Studio Express and read into the database using the ODBC connector, and then write data using the Sqlite ADO.NET connector. I found mostly third-party tools for communicating with JET databases ... JET waas aweful and never gets reset on the back side.

+2
source share

To complement Tony's answers with examples:

Here's how I did the conversion using MDB Tools in 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 instructions and is pretty slow.

A faster alternative is to export / import CSV files. I used this successfully 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"
0
source share

More articles:


All Articles