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

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

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 3.51 to your desktop computer, making sure to select the appropriate installation file for your version of Windows. (probably Windows Driver Installer (MSI))

2. After downloading, double click on mysql-connector-odbc-3.51.12-win32.msi.

3. When the MySQL Connector/ODBC 3.51 - Setup Wizard window appears, click Next.

4. Select the "Typical" installation & click Next.

5. Click Install and wait for the installation to finish. Press Finish.

Congratulations! You have successfully configured MySQL Connector/ODBC 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 and click New.

3. You should now see the Create New Data Source box.

   • Select User Data Source and press Next.
   • Select MySQL ODBC 3.51 Driver and press Next.
   • Click Finish.

4. You should now see the Connector/ODBC - Add Data Source Name box.

   • • In the Data Source Name field, type (yourdomain) .
     • In the Server field, type either www.yourdomain.xxx (substute your information).
     • In the User field, type (your database user id) .
     • In the Password field, type Your password
     • In the Database field, type the name of the database you want to connect to.
     • Click on the Connect Options tab.
     • Click on the Test button. If it says "Success; connection was made!", then everything should be working. If is says "Request returned with SQL_ERROR" or another error message, click on Diagnostics. The output this gives you may be able to help you figure out what went wrong. You should probably double-check all the settings you entered, and the permissions of the user and database you're using.
     • Click OK.
     • Select the connection that you just made.
     • Click OK again.

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

   • 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 Access, you cannot configure fields, tables, or database schema. phpMyAdmin, however, handles such changes very ably.