Search | Directories | Reference Tools
UW Home > UWIN > Computing and Networking > Web > Web Publishing > Using MySQL 

Using Microsoft Access with MySQL

Summary

This article demonstrates how to connect to a MySQL server and edit database records using Microsoft Access. It assumes that you have familiarity with MS Access and that you have already installed MySQL on the UW servers.

Note: The procedures outlined in this article were written using Microsoft Access XP on Windows XP. They may have to be adjusted slightly if you are using another version of Microsoft Access or Windows.

Included on this page:

Installing the MyODBC Driver

The ODBC API (Open Database Connectivity Application Programming Interface) provides a way for client programs such as MS Access to access databases on remote servers. Before you can use Access with your MySQL server, you must thus first install the MyODBC driver. To do so, follow these instructions:

  1. Download MyODBC 2.50 to your desktop computer, making sure to select the appropriate installation file for your version of Windows.

  2. After downloading, unzip the file, and then double click on Setup.exe.

  3. When the MyODBC Ver 2.5 Installation window appears, click Continue.

  4. Select MySQL from the list of available ODBC drivers, and then click OK. Files will begin copying to your computer.

  5. Select sample-MySQL (MySQL) from the list of available ODBC drivers, and then click Add.

  6. When the Add Data Source window pops up, scroll to the bottom of the list of installed ODBC drivers and select MySQL. Click OK.

  7. TDX mysql Driver default configuration window should appear.
    • In the Windows DSN Name field, type sample-MySQL.
    • In the MySQL host (name or IP) field, type either ovid.u.washington.edu (staff/faculty/courses/depts sites) or vergil.u.washington.edu (student sites), depending on where you installed MySQL.
    • Leave the MySQL database name field blank.
    • In the User field, type root.
    • In the Password field, type the root password you chose when you installed MySQL.
    • In the Port field, type the port number you chose when you created your .my.cnf file.
    • Leave the SQL command on connect field blank.
    • Press OK.
    • Click close, and then OK.
    • Close the MyODBC zip file.

Congratulations! You have successfully configured MyODBC to connect to your MySQL server, and you can now link to your MySQL tables with Microsoft Access.

Connecting to a MySQL Database from MS Access; Linking and Editing Tables

Microsoft Access provides two ways to edit the data in your MySQL databases: importing and exporting data, and linking directly to tables. When you import data, you make a copy of a table or query on your local computer; similarly, when you export, you copy information from your local computer back onto your MySQL server. The limitation of this is that you cannot directly edit or overwrite tables in your existing MySQL database. Linking, on the other hand, allows you to make a direct connection to a database on your MySQL server and edit table entries with MS Access. This section shows you how to do just that.

Once you have the MyODBC driver installed, you can connect to your MySQL server and edit database records via a MS Access link. To connect to your MySQL server from MS Access, follow these instructions:

  1. Create a new Access database, or open a pre-existing Access database you want to link to your MySQL server.
  2. Click File -> Get External Data -> Link Tables. This will display the Link dialog box. Find the Files of Type drop-down list at the lower left of the window and click the down arrow. Scroll to the bottom of the list and select ODBC Databases (). The Select Data Source dialog box will appear; it lists the defined data sources for any ODBC drivers installed on your computer. Click on the Machine Data Source tab at the top, select sample-MySQL, and click OK.
  3. You should now see the TCX mysql Driver connect box with the default values you entered when you installed the MyODBC driver. In the MySQL database name field, enter the name of the database you want to import, and click OK.
  4. You should now be presented with a list of all the tables in your database. Select the tables you would like to view or modify and click OK. Access may ask you to select unique identifiers for some tables; this is only necessary if you will be updating records.
  5. The tables you selected should now appear with globe icons in the tables section of your Access database; double-click a table to view or modify its contents. As long as the table has at least one unique identifier, any changes you make will be updated on the MySQL server as you work.

Note: while you can view table and column properties of linked tables in MS Acess, you cannot configure fields, tables, or database schema. phpMyAdmin, however, handles such changes very ably.

References

For more information about MyODBC and using Microsoft Access with MySQL, see MyODBC FAQ: MS Access Related.