Connecting Microsoft Access to MySQL Using an ODBC Driver
This article explains how to connect Microsoft Access to MySQL through the standard ODBC interface. Microsoft Access is a dababase management system that combines the relational database engine with a graphical user interface. Access can be used as a substitution for spreadsheet applications like Excel to organize, store, and retrieve large amounts of related data that can be difficult to manage in spreadsheets.
In Microsoft Access, you can connect to your MySQL data either by importing it or creating a table that links to the data. Devart ODBC drivers support all modern versions of Access. It is assumed that you have already installed and configured a DSN for ODBC driver for MySQL. For the purpose of this article, we tested an ODBC connection to MySQL through our ODBC drivers in Microsoft Access 2003, Microsoft Access 2007, Microsoft Access 2010, Microsoft Access 2013, Microsoft Access 2016, Microsoft Access 2019. The following steps describe how to use Microsoft Access 2019 to import or link to your data in MySQL.
Importing MySQL Data Into Microsoft Access Through an ODBC Connection
- Open your Microsoft Access database.
- Select the External Data tab in the ribbon.
- Expand the New Data Source drop-down and select From Other Sources, then select ODBC Dababase.
- In the Get External Data - ODBC Database dialog box, select Import the source data into a new table in the curent database, and click OK.
- In the Select Data Source dialog box, select the Machine Data Source tab.
- Select the DSN that you have configured for MySQL and click OK.
- In the Import Objects dialog box, select the tables that you want to import, and click OK.
- If the database objects have been successfully imported, you should the see the corresponding message in the dialog box. If you want to save the import steps to quickly repeat the process without using the wizard at a later time, select the Save import steps checkbox. Click Close.
- The imported tables should appear in the Tables navigation pane on the left.
- Double-click on the needed table to display its contents.
Linking to MySQL Data in Microsoft Access Through an ODBC Connection
- Open your Microsoft Access database.
- Select the External Data tab in the ribbon.
- Expand the New Data Source drop-down and select From Other Sources, then select ODBC Dababase.
- In the Get External Data - ODBC Database dialog box, select Link to the data source by creating a linked table.
- In the Select Data Source dialog box, select the Machine Data Source tab.
- Select the DSN that you have configured for MySQL and click OK.
- In the Link Tables dialog box, select the table or tables that you want to link to, and click OK.
- The Select Unique Record Identifier dialog box will prompt you to choose a field or fields that uniquely identify each record in the table. To avoid inconsistencies, it is recommended to select the primary key in the MySQL table as the unique record identifier. You are linking multiple tables, you will be prompted to select unique record identifiers for each of the selected tables.
- The linked tables should appear in the Tables navigation pane on the left.
- Double-click on the needed table to display its contents.
