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 AC Cess. One of the most common questions I ' m asked is ' How do I upload my data ' i-Access database to my MySQL database? ' This is far easier than with you and are done using a 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 "the" the "I" is to provide the "capability for your" Windows system to communicate with the remote MySQL server. This is the 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'll list the steps I took to migrate this data.
- In the Windows Control Panel (or equivalent in WinME, Windows, or XP) Open the ODBC Data Source Administrator. You'll want to create a new System data source.
- Choose "Add", then select the MySQL driver. You should the following form. Give it a meaningful DSN name, in me case I called it remotezipcodes. The DSN is how would identify this connection when you are want to use it from another application. Fill in the rest of the necessary information correctly. One tip, MySQL is case sensistive. Make sure to the username, password, and database case correct.
- This is my source data. A single Access table containing zipcodes by the city and the along with their latitude and longitude.
- We want to "export" our data to the remote MySQL server. Select the table want to export and choose "File/save as/export ..." from the main Access menu, then choose ' to ' an extern Al file or database.
- The "Save Table" dialog box would then open and by default'll try to export your table to another Access MDB. You change the format of the ' destination by choosing ' Save as type combo box. We want to select ODBC Databases (). It is probably at the very bottom of the list.
Next, this little window would open
To allow for the name of the table that'll be created in the remote database.
- When you select ' OK ' You should then the your list of ODBC data sources. Select the DSN you created in step 2.
- Once you select the data source, the export begins immediately. Being patient, it may take a while, but it does work!
Try experimenting with importing and especially linking Too! You'll find this can read and actually query data from Oracle, PostgreSQL (Me other favorite database), Excel spread sheets, and even text files. To link a table in an Access MDB, on the Table tab select "New" then "link" .... I'd figure out the rest!
