Tutorial for migrating data from MS Access to MySQL (English Oh, be prepared)

Source: Internet
Author: User
Tags dsn odbc mysql access database mysql database
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.
  1. 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.

  2. 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.

  3. This is my source data. A single Access table containing zipcodes by the city and the along with their latitude and longitude.

  4. 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.
  5. 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.
  6. When you select ' OK ' You should then the your list of ODBC data sources. Select the DSN you created in step 2.
  7. 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!


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.