Documentation Home
Connectors and APIs Manual
Download this Manual
PDF (US Ltr) - 4.5Mb
PDF (A4) - 4.5Mb

5.6.4.3 Using Microsoft Access as a Front-end to MySQL

You can use Microsoft Access as a front end to MySQL 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.

To create a linked table:

  1. Open the Access database that you want to link to MySQL.

  2. On the External Data tab, choose ODBC Database.

    Figure 5.23 External Data: ODBC Database

    Content is described in the surrounding text.

  3. In the Get External Data dialog box that appears, choose Link to the data source by creating a linked table and click OK.

    Figure 5.24 Get External Data: Link To ODBC Database Option Chosen

    Shows the "Get External Data" dialog with two options: Import the source data into a new table in the current database, and Link to the data source by creating a linked table (selected).


  4. The Select Data Source dialog box appears; 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 DSN you want to link your table to. To define a new DSN for Connector/ODBC instead, click New and follow the instructions in Section 5.5.3, “Configuring a Connector/ODBC DSN on Windows”; double click the new DSN after it has been created.

    Figure 5.25 Selecting An ODBC Database

    Shows the Select Data Source dialog with two tabs: "File Data Source" and "Machine Data Source." The Machine Data Source tab is selected and displays three columns: Data Source Name, Type, Description. The selected row has "MySQL 5.7" defined as the Data Source Name, and "System" as the Type.

    If the ODBC data source that you selected requires you to log in, enter your login ID and password (additional information might also be required), and then click OK.

  5. Microsoft Access connects to the MySQL server and displays the list of tables that you can link to. Choose the tables you want to link to (or click Select All), and then click OK.

    Figure 5.26 Link Tables Dialog: Selecting Tables to Link

    Content is described in the surrounding text.

    Notes

    • If no tables show up for you to select, it might be because you did not choose the Database to connect to when you defined or logged in to the DSN. Reconfigure the DSN and specify the Database to connect to (see Section 5.5.3, “Configuring a Connector/ODBC DSN on Windows” for details), or choose a Database when you log in to the DSN.

    • If your database on Access already has a table with the same name as the one you are linking to, Access will append a number to the name of the new linked table.

  6. If Microsoft Access is unable to determine the unique record identifier for a table automatically, it will ask you to choose a column (or a combination of columns) to be used to uniquely identify each row from the source table. Select the column[s] to use and click OK.

    Figure 5.27 Linking Microsoft Access Tables To MySQL Tables, Choosing Unique Record Identifier

    Shows the "Select Unique Record Identifier" dialog with a list of fields in the selected table. In this example, the table name is cats2 and the unique fields are CatID, CatName, OwnerID, and Birthday. The available buttons are "OK" and "Cancel".

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

Use the following procedure to view links or to refresh them when the structures of the linked tables have changed.

To view or refresh links:

  1. Open the database that contains links to MySQL tables.

  2. On the External Data tab, choose Linked Table Manager.

    Figure 5.28 External Data: Linked Table Manager

    Shows "Linked Table Manager" under the "External Data" tab in Microsoft Access. Underneath are two panes: The All Access Objects pane shows the available tables, and the second pane shows data from the table. The "cats2" table is selected.

  3. The Linked Table Manager appears. Select the check box for the tables whose links you want to refresh. Click OK to refresh the links.

    Figure 5.29 External Data: Linked Table Manager Dialog

    Shows the "Linked Table Manager" dialog with the cats2 table selected with its associated check box selected.

    If the ODBC data source requires you to log in, enter your login ID and password (additional information might also be required), and then click OK.

Microsoft Access confirms a successful refresh or, if the tables are not found, returns an error message, in which case you should update the links with the steps below.

To change the path for a set of linked tables (for pictures of the GUI dialog boxes involved, see the instructions above for linking tables and refreshing links) :

  1. Open the database that contains the linked tables.

  2. On the External Data tab, choose Linked Table Manager.

  3. In the Linked Table Manager that appears, select the Always Prompt For A New Location check box.

  4. Select the check box for the tables whose links you want to change, and then click OK.

  5. The Select Data Source dialog box appears. Select the new DSN and database with it.


PREV   HOME   UP   NEXT
Download this Manual
PDF (US Ltr) - 4.5Mb
PDF (A4) - 4.5Mb