How I migrated my Microsoft Access database into MySQL

In my school life, I started my family library — Gyanoshudha Gronthagar. When I was a college student I learned Microsoft Access as a database management software as a study curriculum. And immediately I thought to use it to make a digital database of my library collections. Since then I managed the database in MS Access for many years. In my job life I became a software engineer and worked with MySQL a lot. Then I decided to make an online database of my MS Access db using MySQL. And when I needed to migrate the MS Access db into MySQL.

This article is not a paid sponsorship or for marketing at all. This article is to show gratitude for a good solution once I used for a non-profit.

It’s possible to copy & paste the data from MS Access to MySQL as I did not have too much information, but about 350+ records only. But I was looking for an automated solution to do the job. I found some of the working solutions but many of them were paid and I could not afford to pay for a non-profit cause at that time. So I was looking for a free solution.

At that time RebaseData came to the rescue. Though rebase data is a paid solution now — I can’t remember whether I tried their solution at that time or it was free then. Right now (August 2023) they declared pricing for their services where a 10MB file can be converted with a free trial.

Get Mayeenul Islam’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

I used their Access to MySQL conversion online tool for converting my MS Access db into MySQL and that was awesome. Their conversion tool did what it meant and I found my desired MySQL database after conversion.

I did not use the converted database as it was, because for the web database, I had to design the database on my own. I then prepared a PHP script to read the converted MySQL Database and then insert the data into my newly designed database into related tables. After then I moved the necessary tables from the converted database into my own database for the data migration.

gg_database:
- _books
- _topics
- _authors
- books
- topics
- authors

The underscored tables (eg. _books) were the converted tables and the others (eg. books) were my newly created tables. I ran that script once to move all the data from the converted tables into my newly created tables. But that was a trial and error method as all the edge cases could not be predicted earlier. So if anything goes wrong during the migration, I then truncated my new tables and fix the script and/or data to make the migration smooth next time. Using the trial and error method after some failure I then successfully migrated all my data from the converted tables into my newly created tables.

And there went my journey of converting my library database from MS Access into MySQL. And the final version of the database for a web portal can be accessed from: gg.nanodesignsbd.com.

—

If you find my article enjoyable, you can encourage me by hitting the 👏 clap button, and don’t forget to follow me for upcoming articles. 🙂