22.1.5.4. Using Connector/ODBC with Microsoft Access

You can use MySQL database with Microsoft Access using Connector/ODBC. The MySQL database can be used as an import source, an export source, or as a linked table for direct use within an Access application, so you can use Access as the front-end interface to a MySQL database.

22.1.5.4.1. Exporting Access Data to MySQL

To export a table of data from an Access database to MySQL, follow these instructions:

When you open an Access database or an Access project, a Database window appears. It displays shortcuts for creating new database objects and opening existing objects.
Click the name of the table or query to export, and then in the File menu, select Export.
In the Export Object Type Object name To dialog box, in the Save As Type box, select ODBC Databases () as shown here:
In the Export dialog box, enter a name for the file (or use the suggested name), and then select OK.
The Select Data Source dialog box is displayed; it lists the defined data sources for any ODBC drivers installed on your computer. Click either the File Data Source or Machine Data Source tab, and then double-click the Connector/ODBC or Connector/ODBC 3.51 data source to export to. To define a new data source for Connector/ODBC, please Section 22.1.4.3, "Configuring a Connector/ODBC DSN on Windows".

Note

Ensure that the information that you are exporting to the MySQL table is valid for the corresponding MySQL data types. Values that are outside of the supported range of the MySQL data type but valid within Access may trigger an "overflow" error during the export.

Microsoft Access connects to the MySQL Server through this data source and exports new tables and or data.

22.1.5.4.2. Importing MySQL Data to Access

To import a table or tables from MySQL to Access, follow these instructions:

Open a database, or switch to the Database window for the open database.
To import tables, on the File menu, point to Get External Data, and then click Import.
In the Import dialog box, in the Files Of Type box, select ODBC Databases (). The Select Data Source dialog box lists the defined data sources The Select Data Source dialog box is displayed; it lists the defined data source names.
If the ODBC data source that you selected requires you to log on, enter your login ID and password (additional information might also be required), and then click OK.
Microsoft Access connects to the MySQL server through ODBC data source and displays the list of tables that you can import.
Click each table to import, and then click OK.

22.1.5.4.3. Using Microsoft Access as a Front-end to MySQL

You can use Microsoft Access as a front end to a MySQL database by linking tables within your Microsoft Access database to tables that exist within your MySQL database. When a query is requested on a table within Access, ODBC is used to execute the queries on the MySQL database instead.

To create a linked table:

Open the Access database that you want to link to MySQL.
From the File, choose Get External Data->Link Tables.
From the browser, choose ODBC Databases () from the Files of type pop-up.
In the Select Data Source window, choose an existing DSN, either from a File Data Source or Machine Data Source.You can also create a new DSN using the New... button. For more information on creating a DSN see Section 22.1.4.3, "Configuring a Connector/ODBC DSN on Windows".
In the Link Tables dialog, select one or more tables from the MySQL database. A link will be created to each table that you select from this list.
If Microsoft Access is unable to determine the unique record identifier for a table automatically then it may ask you to confirm the column, or combination of columns, to be used to uniquely identify each row from the source table. Select the columns to use and click OK.

Once the process has been completed, you can now build interfaces and queries to the linked tables just as you would for any Access database.

Use the following procedure to view or to refresh links when the structure or location of a linked table has changed. The Linked Table Manager lists the paths to all currently linked tables.

To view or refresh links:

Open the database that contains links to MySQL tables.
On the Tools menu, point to Add-ins (Database Utilities in Access 2000 or newer), and then click Linked Table Manager.
Select the check box for the tables whose links you want to refresh.
Click OK to refresh the links.

Microsoft Access confirms a successful refresh or, if the table wasn't found, displays the Select New Location of <table name> dialog box in which you can specify its the table's new location. If several selected tables have moved to the new location that you specify, the Linked Table Manager searches that location for all selected tables, and updates all links in one step.

To change the path for a set of linked tables:

Open the database that contains links to tables.
On the Tools menu, point to Add-ins (Database Utilities in Access 2000 or newer), and then click Linked Table Manager.
Select the Always Prompt For A New Location check box.
Select the check box for the tables whose links you want to change, and then click OK.
In the Select New Location of <table name> dialog box, specify the new location, click Open, and then click OK.