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
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.
I did have to switch to my Windows PC to do this though. But it worked well, producing many GiB file.