Exchanging Data Between MySQL and Microsoft Access
Problem
You want to exchange information between MySQL and Access.
Solution
To use information stored in MySQL, connect to the MySQL server directly from Access. To transfer information from Access to MySQL, use a utility that can perform the transfer directly, or else export tables from Access into files and import the files into MySQL.
Discussion
MySQL and Access both understand ODBC, so you can connect to MySQL
directly from Access. By making an
ODBC connection, Access
becomes a frontend through which you use MySQL databases. The
mysql.com
MyODBC area contains
a lot of useful information:
http://www.mysql.com/products/myodbc/
An excellent description of the procedures for setting up ODBC and for connecting from Access to MySQL over ODBC may be found in W.J. Gilmore’s article at the DevShed web site:
http://www.devshed.com/Server_Side/MySQL/ODBC/
If your tables currently are in Access and you want to move them into MySQL, you’ll need to create tables in MySQL to hold the information and then import the Access data into those tables. A couple of good free tools that can make this easier are DBTools and MySQLFront. They can examine the structure of tables in an Access database, create the corresponding MySQL tables for you, and copy the data directly into MySQL.
You can also choose to export Access tables to files and then import the files into MySQL. (This may be necessary, for example, if your MySQL server is on a different machine ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.