Migrating an old MS Access database to MySQL [UPDATED]

August 3, 2009

I’m in the process of migrating an old website I made almost 8 years ago from it’s current unbearable platform of Windows, ASP and MS Access to some sort of *nix, PHP and MySQL. Tonight I decided to combat that MS Access database and transform it into a real database. To help me accomplish this I used the excellent tools provided by the mdbtools project.

First I had to export the schema of the database to a format readable by MySQL:

mdb-schema -S mydb.mdb mysql > mydb.sql

This creates an SQL-file containing the schema in plain readable SQL. Very nice.

Next I had to export the actual data from the tables in the MS Access database, like this:

TABLES=`mdb-tables mydb.mdb`
for T in $TABLES; do
mdb-export -I -R';\n' mydb.mdb $T >> mydb.sql

The first line stores the names of all the tables in the variable TABLES. Then I simply loop through all of them and execute the mdb-export command on all of them, writing the output to the end of the schema-file.

In case I need to do this again in the future I put it all together in a script like this:


#Check for correct number of arguments
if [ ! $# -eq 2 ]; then
echo "Usage: access2mysql.sh DBFILE OUTPUTFILE"
echo "Example: access2mysql.sh msaccess.mdb mysql.sql"
exit 1
#Check that DBFILE really exists
if [ ! -f $DBFILE ]; then
echo "$DBFILE does not exist."
exit 1

#All is good, here we go!

#Create schema
mdb-schema -S $DBFILE mysql > $OUTFILE

#Export table data
TABLES=`mdb-tables $DBFILE`
for T in $TABLES; do
mdb-export -I -R';\n' $DBFILE $T >> $OUTFILE
#Clean up some Windows-character stuff
dos2unix $OUTFILE
exit 0

Now all I have to do is create a MySQL-database and write my newly created SQL-script into it and I’m good to go!

Before I could get the SQL-script to work I had to fire off a couple of sed-statements to fix the fact that I had used some illegal words as names for some of the table-columns, “type” and “condition” to be specific. No biggie though. Also, mdb-schema didn’t add “AUTO_INCREMENT” to the fields that were auto incrementing in the MS Access database so I had to do that manually.