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.
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
tableorqueryyou want to export, and then in theFilemenu, selectExport.In the
Export Object Typedialog box, in theObject nameToSave As Typebox, selectODBC Databases ()as shown here:
In the
Exportdialog box, enter a name for the file (or use the suggested name), and then selectOK.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 that you want 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”.
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.
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
Filemenu, point toGet External Data, and then clickImport.In the
Importdialog 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 sourceand displays the list of tables that you canimport.Click each table that you want to
import, and then clickOK.
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 , choose .
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 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 you want to use and click .
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
Toolsmenu, point toAdd-ins(Database Utilitiesin Access 2000 or newer), and then clickLinked 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
Toolsmenu, point toAdd-ins(Database Utilitiesin Access 2000 or newer), and then clickLinked Table Manager.Select the
Always Prompt For A New Locationcheck 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, clickOpen, and then clickOK.