access|mysql
Tutorial for migrating data from MS Access to MySQL
One of the main reasons I started FreeSQL.org is to open up newbie users to the wide world of enterprise-level database access. One of the most common questions I'm asked is "how do I upload my data from my Access database to my MySQL database?" This is far easier than you think and is done using an often overlooked feature of Microsoft Access. Namely, the ability to import, export, and link to non-Access data sources. For now lets just consider MySQL.
The first step is to provide the capability for your windows system to communicate with the remote MySQL server. This is done using MyODBC. If you don't have it installed already, get it from here.
Recently I had to move a database of zipcodes to my MySQL server. I will list the steps I took to migrate this data.
- In the windows 98 control panel (or equivalent in WinME, Windows 2000, or XP) open the ODBC Data Source Administrator. You will want to create a new System data source.
- Choose "Add", then select the MySQL driver. You should see the following form. Give it a meaningful DSN name, in my case I called it remoteZipCodes. The DSN is how you will identify this connection when you want to use it from another application. Fill in the rest of the necessary information correctly. One tip, MySQL is case sensistive. Make sure you get the username, password, and database case correct.
- Here is my source data. A single Access table containing zipcodes by city and state, along with their latitude and longitude.
- We want to "export" our data to the remote MySQL server. Select the table you want to export and choose "file/save as/export…" from the main Access menu, then choose "to an external file or database".
- The "save table" dialog box will then open and by default will try to export your table to another Access MDB. You change the format of the destination by choosing from the "save as type" combo box. In our case we want to select "ODBC Databases ()". It is probably at the very bottom of the list.
Next, this little window will open
to allow you to change the name of the table that will be created in the remote database. - When you select "OK" you should then see your list of ODBC data sources. Select the DSN you created in step 2.
- Once you select the data source, the export begins immediately. Be patient, it may take a while, but it does work!
Try experimenting with importing and especially linking too! You will find that you can read and actually query data from Oracle, PostgreSQL (my other favorite database), Excel spreadsheets, and even text files. To link a table into an Access MDB, on the Table tab select "new" then "link"....I think you'll figure out the rest!
