Project 7-3 Import and Link MySQL Data in Access

This project will give you experience making a DSN connection using ODBC, and then using it to import and link MySQL data in Access.

Step by Step

1. In Windows, select Start Settings Control Panel. Double-click Administrative Tools, and then open Data Sources (ODBC) by double-clicking it.

2. Click the System DSN tab, and then click the Add button.

3. Scroll down to the MySQL ODBC driver and select it. (If the MySQL ODBC is not listed, see the instructions for installing MyODBC drivers in Module 1.) Then click Finish.

4. For the Data Source Name , enter duckwear (so it will be easy to remember to which database the particular connection refers).

5. For the Host/Server Name (or IP Address), enter localhost , or enter the IP address of the MySQL Server if MySQL and Access are not on the same machine.

6. For the Database Name, enter duckwear .

7. Enter your user name and password for the MySQL database. Click the OK button.

8. Now that the DSN connection has been created, open Access to import the data. Select File Get External Data Import.

9. When the Open dialog box opens, change the File Type to ODBC Databases().

10. Click the Machine Data Sources tab, highlight the duckwear DSN, and then click the OK button.

11. Click the duck_sales table.

    Note  

    The Select All button will allow you to select all the tables at once rather than selecting them individually.

12. Click the OK button. Now you have a separate copy of your data in your local Access database.

    Caution  

    When you add records in the local copy in Access, the changes will not be reflected in your MySQL database.

13. To link to the MySQL data, from Access, select File Get External Data Link Tables.

14. In the dialog box, change the File Type to ODBC Databases().

15. Click the Machine Data Sources tab, highlight the duckwear DSN, and then click the OK button.

16. Click the duck_sales table, and then click the OK button. You now have a link to your MySQL table data from the Access program. Any changes you make to the data here will be reflected in your MySQL database.

Project Summary

This project showed you how to make a DSN connection using MyODBC and use it to import data directly from MySQL to Access and to link to the data in MySQL from inside Access. Importing the data allows you to manipulate and examine it outside your database environment. This can be useful for two reasons: By placing the data in a separate environment, you remove the risk of corrupting the original data. Linking to MySQL through Access also allows a person familiar with Access to examine and change the data in MySQL (but not the table format) without having to learn another GUI. While this is not a long term solution ”a MySQL-specific GUI like MySQLCC will be an all-around better choice, practically speaking ”it may be the quickest way for users to work with data that has been migrated from Access to MySQL. Data stored in Access can be migrated to MySQL by exporting it to a delimited file and then importing that file into MySQL using the file as input in the normal manner.