Convert MDB to SQLite

Why SQLite instead of CSV

A folder of CSV files loses the database. Each table becomes a separate file, and every value becomes text. SQLite keeps the tables together in one file and keeps the types.

Two things survive that CSV cannot carry:

• Null stays Null. CSV writes a Null as an empty field, which looks the same as empty text. In SQLite the two stay different.
• Binary data stays binary. OLE Object fields are stored as BLOBs. In CSV they become a placeholder.

You can query the result with SQL straight away. The sqlite3 command ships with macOS, and Python reads SQLite without extra packages.

Converting a database

1. Open the database. Choose the .mdb or .accdb file. An encrypted .accdb asks for its password.
2. Pick the tables. Select them all, or tick the ones you need.
3. Choose SQLite as the output.
4. Export. Progress shows table by table. Cancelling stops between tables and leaves no partial file behind.

A folder of databases works the same way. Each one becomes its own .sqlite file.

Checking the result

Open Terminal and query the file:

sqlite3 Database.sqlite
.tables
SELECT COUNT(*) FROM "Order Details";

Access table names often contain spaces. Put them in double quotes, as above.

Compare the counts with the summary saved next to the export. It lists the row count for every table and anything that was skipped.

How Access types are stored

Currency is stored as text on purpose. A floating-point column would round some amounts. Cast the column when you calculate, for example SUM(CAST(Amount AS REAL)).

The date format is the one SQLite’s own date functions expect. date(), strftime() and date comparisons work on it directly.

What does not carry over

MDB File Viewer copies the data, not the design around it. Not exported:

• Relationships, primary keys and indexes
• Saved queries
• Forms, reports, macros and VBA

For a reporting copy or a one-off migration, the tables are usually enough. If you need the schema too, look at mdbtools.

The free route: mdbtools

mdbtools can write a SQLite schema and the data as SQL statements. Unlike MDB File Viewer, its schema output includes indexes.

brew install mdbtools
mdb-schema database.mdb sqlite > schema.sql
mdb-tables -1 database.mdb | while read -r t; do
  mdb-export -I sqlite database.mdb "$t"
done > data.sql
sqlite3 database.sqlite < schema.sql
sqlite3 database.sqlite < data.sql

It is free and offline. It does not open encrypted .accdb files.