[+/-]
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 table
or
query
you want to export, and then in
the File
menu, select
Export
.
In the Export Object Type
dialog box, in the
Object
name
ToSave 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 that you want to export to. To define a new data source for Connector/ODBC, please Section 21.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 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 that you want to
import
, and then click
OK
.
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 21.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 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
.
User Comments
Add your own comment.