Exporting Data from Microsoft Access to MySQL

Many novice database users start with Microsoft Access, and a common use of ODBC is to enable an upgrade to MySQL, exporting data from Access. To do so, perform the following steps (steps 1 and 2 are only required the first time you do this):

1. Install the MyODBC driver.

2. Set up a data source pointing to the MySQL server you want to export the data to, as described in the earlier section "Setting Up a Data Source on Windows."

3. Load Microsoft Access.

4. Open the Microsoft Access database window, and select the table you want to export.

5. Click File ® Export and choose ODBC Databases () from the Save as Type drop-down list.

6. Choose a new name if you want to change the table name, and then click OK.

7. Select the data source you defined in step 2 and click OK.

8. If the data source connection details are not correct, you'll need to change them, which you can do at this point as well. Remember that MySQL permissions have to be granted to allow access. See Chapter 14, "Database Security" for details on this.