MSU News

University Communications > Web and Digital Communications > Tutorials > Connecting (Linking) ACCESS to MySQL

MSU Web Communications

Montana State University
P.O. Box 172220
Bozeman, MT 59717-2220

Tel: (406) 994-5036
Fax: (406) 994-4102
Location: 419 Culbertson

Director:

Jacob Dolan
jake@montana.edu

Connecting (Linking) ACCESS to MySQL

Step 1:

Ensure that your IP address has been given proper permissions to access the MySQL database. To make this request, contact Web and Digital Communications and reference this page.

Step 2:

Download and install (if you have not done this previously) the Connector/ODBC driver for MySQL. It can be found here:
http://dev.mysql.com/downloads/connector/odbc/5.1.11html#downloads
Note: there may be more recent versions of this driver by the time you are reading this
Troubleshoot: If your system is 64-bit, try downloading the 32-bit connector and install as normal. Access the ODBC program via C:\Windows\SysWOW64\odbcad32.exe and set up your connector for use in Access. When you run Access, the new connector should now be available in Step 8.

Step 3:

Open Microsoft ACCESS and create a new database. If you are using an existing database, skip to Step 5.
Open Microsoft ACCESS and create a new database

Step 4:

Give it a name. Click the 'Create' button.
Give it a name. Click the 'Create' button

Step 5:

With the new database open, click on the 'External Data' tab.
With the new database open, click on the 'External Data' tab

Step 6:

Under the 'Import' grouping, click 'More' and choose 'ODBC Database'.
Under the 'Import' Group, click 'More' and choose 'ODBC Database'

Step 7:

Select 'Link to the data source by creating a linked table' and click the 'OK' button.
Select 'Link to the data source by creating a linked table' and click the 'OK' button.

Step 8:

Click the 'Machine Data Source' tab and click the 'New' button.
Click the 'Machine Data Source' tab and click the 'New' button'

Just ignore the following warning if you receive it and click 'OK button.
Just ignore the following warning if you receive it and click 'OK button

Step 9:

Select 'User Data Source' and click the 'Next' button.
Select 'User Data Source' and click the 'Next' button

Step 10:

Select 'MySQL ODBC 3.51 Driver' and click the 'Next' button.
Select 'MySQL ODBC 3.51 Driver' and click the 'Next' button

Step 11:

Click the 'Finish' button
Click the 'Finish' button

Step 12:

Customize the 'Connector/ODBC' to fit your database connection. All fields of the 'Login' tab should be filled out. If you need this information, please contact Web and Digital Communications and reference this page.
Customize the 'Connector/ODBC' to fit your database connection

Additional steps will be created to describe the process of creating a secure connection.

Step 13:

Click the 'Test' button to verify that the connection is successful (click 'OK' to exit out of the popup window).
Click the 'Test' button to verify that the connection is successful

Then click the 'Ok' button.
Then click the 'Ok' button.

Step 14:

In the 'Select Data Source' window choose the new source you just created from the list and click the 'OK' button.
In the 'Select Data Source' window choose the new source you just created from the list and click the 'OK' button

Step 15:

Choose which tables to link (you can choose one or more), then click the 'OK' button.
Choose which tables to link (you can choose one or more), then click the 'OK' button

Step 16:

You should now be able to see the selected tables. If you have been given the proper permissions you should be able to edit information from within those tables as well.
You should now be able to see the selected tables.  If you have been given the proper permissions you should be able to edit information from within those tables as well.