Converting MS Access to MySQL

For small and large database projects, Microsoft Access works wonderfully. If you have a database with hundreds of thousands of records, maybe even connecting to other databases remotely. Security isn’t too much of a problem (no Access database is truly secure), and with a wide range of options for publishing and reporting on your data.

That is, until you try to put it online. That”s where converting your Access database to MySQL comes in.

While Microsoft Access is great as a desktop solution, it weakens greatly when transferred online. The limits on users and speed still exist, and it”s not built to work well on platforms other than Microsoft. Unfortunately for Microsoft Access, most web servers run a form of Unix, not Windows.

Enter MySQL.

MySQL is a relational database system designed specifically for use online and housed on the most common servers online. It”s fast, robust, clean, free, and fairly intuitive to work with as long as you have a very solid understanding of all things SQL. Converting to MySQL from Microsoft Access isn”t without it”s set of problems, however there are a few things you can do to make sure that this conversion goes smoothly.

1. Organize and clean your Microsoft Access database before converting to MySQL! This can”t be stressed enough and it”s pretty much the sole reason your conversion from Access to MySQL will fail or succeed. Think of upgrading from Access to MySQL like a complicated high-wire act. If one person has trouble going across that wire, chances are making him ride a bicycle across it while balancing a ball on his nose will only end up in disaster … and a broken bicycle. Re-design your Microsoft Access database if you have to, but be absolutely sure that your database is designed flawlessly.

2. Set up your MySQL database. Just as making sure your Access database is designed flawlessly, your MySQL database must be designed just as flawlessly. MySQL has different data types than Microsoft Access does - you won”t find memo fields, for instance - so be sure that your existing data will fit into the structure you build for MySQL. Otherwise, all you”re going to get is a mess of mish-mashed data.

3. Export your Microsoft Access data to text files. Fortunately, MySQL has a very handy interface called phpMyAdmin. This allows you to interact directly with your tables through a somewhat graphical UI. All you need to have are tables and fields set up that match your data, and you can click, “Import Data from Text File” through phpMyAdmin. Browse to the text file, import it, and congratulations, your data is online!

While there is certainly more details to be learned regarding the process of converting from Microsoft Access to MySQL, always keep in mind the fundamentals. Databases that are designed perfectly will circumvent 95% of the problems that generally befall poorly designed ones. Converting Access to MySQL isn”t terribly difficult or time consuming, but it can be very tricky. Back up your data, make sure you”ve designed databases properly on both sides, and your data could be up in no time. Then, it”s on to PHP!

Author: David Badurina, President of Blue Moose Technology, LLC, is a relational database design expert specializing in turning hideous data nightmares into efficient, intuitive database applications.