Connecting to a MySQL Database with MS-Access

These instructions were formulated using Microsoft Access 2002 on Windows XP. The process may work slightly differently with a different Access release or Windows version.

Please note that your database tables must already exist on the MySQL server in order to view them with MSAccess. You may use MSAccess as a data-entry tool, but not as a database design tool.

  1. First, you will need to install the MyODBC ODBC driver. Download the appropriate version for your PC from MySQL. After downloading, unzip the file and run setup.exe. This will install the MySQL ODBC driver.
  2. Open the Microsoft Access database you wish to link to your MySql database (or create a new, blank Access database).
  3. Select File | Get External Data | Link Tables. This will display the Link dialog box. Find the Files of Type dropdown box at the bottom of the window and click the down arrow. Scroll all the way down and select ODBC Databases (). You should now get the Select Data Source dialog box. Choose the Machine Data Source tab. Click New
  4. In the Create Datasource dialog box that appears, click Next to accept the default setting for User Data Source. Scroll down the list of drivers and select MySQL ODBC 3.xx driver. Click Next then Finish.
  5. In the DSN Configuration window, enter a Data Source Name of your choice. Change or fill in the host name or IP address for the machine where your MySQL database is located (e.g., dbm2.itc.virginia.edu). Put the name of your database in the MySQL database name box, which should be preceded by your computing ID_ followed by the name of your database (e.g., mst3k_mydatabasename). Fill in your MySQL userid and password, and click OK twice.
  6. You should now be presented with a list of all the tables in your database. Select the ones you want and click OK.
  7. The tables you selected should now appear in the tables section of your Access database. You can open the tables or write queries against them. You should be able to insert, update or delete rows. You can look at table and column properties in the design view, but you won't be able to change any of the properties through Access.

© 2009 by the Rector and Visitors of the University of Virginia.

The information contained on the University of Virginia’s Department of Information Technology and Communication (ITC) website is provided as a public service with the understanding that ITC makes no representations or warranties, either expressed or implied, concerning the accuracy, completeness, reliability or suitability of the information, including warrantees of title, non-infringement of copyright or patent rights of others. These pages are expected to represent the University of Virginia community and the State of Virginia in a professional manner in accordance with the University of Virginia’s Computing Policies.