Exporting Access Data to MySQL

source: http://dev.mysql.com/doc/


http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-walkthrough.html

On system ALPHA (the MySQL server) follow these steps:

1. Start the MySQL server.
2. Use GRANT to set up an account with a user name of myuser that can connect from system BETA using a password of myuser to the database test:

   GRANT ALL ON test.* to 'myuser'@'BETA' IDENTIFIED BY 'mypassword';
   

   For more information about MySQL privileges, refer to MySQL User Account Management.

On system BETA (the Connector/ODBC client), follow these steps:

1. Configure a Connector/ODBC DSN using parameters that match the server, database and authentication information that you have just configured on system ALPHA.

   Parameter	Value	Comment
   DSN	remote_test	A name to identify the connection.
   SERVER	ALPHA	The address of the remote server.
   DATABASE	test	The name of the default database.
   USER	myuser	The user name configured for access to this database.
   PASSWORD	mypassword	The password for myuser.

2. Using an ODBC-capable application, such as Microsoft Office, connect to the MySQL server using the DSN you have just created. If the connection fails, use tracing to examine the connection process. See Section 5.8, “Getting an ODBC Trace File”, for more information.

/////////////////////

6.4.1 Exporting Access Data to MySQL

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

1. 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.
   
2. Click the name of the table or query to export, and then in the File menu, select Export.
3. In the Export Object Type Object name To dialog box, in the Save As Type box, select ODBC Databases () as shown here:
   
4. In the Export dialog box, enter a name for the file (or use the suggested name), and then select OK.
5. 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 5.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.