Convert Access (.mdb) to MySQL

Having recently needed to convert a Microsoft Access database into (something usable?!) an SQL format, I thought I’d mention the findings. This is mainly focusing on .mdb files but might work with newer the accdb files. This approach might also work for many kinds of SQL db, but for simplicity we were importing into a MariaDB instance - so MySQL essentially.

There’s a few options you can go down:

1. Exporting the data from Access#

You can export the data as csv or export directly into another database. This might work well if you already have a MS SQL db setup. But didn’t work out well for me.

2. MDB Tools & Bash#

#!/bin/bash
TABLES=$(mdb-tables -1 $1)

for t in $TABLES
do
    echo "DROP TABLE IF EXISTS $t;"
done

mdb-schema $1 mysql

for t in $TABLES
do
    mdb-export -D '%Y-%m-%d %H:%M:%S' -I mysql $1 $t
done

One approach on Linux is to use the mdb-tools package to help export them. This was a very convenient method, but the file produced didn’t import correctly (lots of invalid syntax and ‘Mysql server gone away’).

This did, however, work very well as a way of exporting to csv. So if you’re good with csv files, I’d recommend just using mdb-tools and mdb-export.k

3. MDB to SQL#

In the end, the way that worked best was using a tool called ‘Access to MySQL’ (sort of does what the name suggests!) Instead of connecting to the database, it conveniently just produces a dump file.

Go to Website / Download Link

I did have to switch to my Windows PC to do this though. But it worked well, producing many GiB file.