10.39 Exchanging Data Between MySQL and Microsoft Access
10.39.1 Problem
You want to
exchange information between MySQL and Access.
10.39.2 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.
10.39.3 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 and doesn't
allow connections from your Windows box.) If you elect to go this
route, some of the issues you'll need to consider
are the file format to use, date format conversion, and how to create
the MySQL tables for the data if the tables don't
already exist. Several of the scripts described earlier in the
chapter (such as cvt_file.pl,
cvt_date.pl, and
guess_table.pl) can provide assistance in
dealing with these issues. The procedure for importing an Access
table into MySQL might go something like this:
Export the table from Access in some text format, perhaps including
the column labels. Should you need to transform the file with other
utilities that assume tab-delimited, linefeed-terminated input, it
will be most useful to export in that format.
If the table contains dates and you did not export them in ISO
format, it will be necessary to convert them for MySQL.
cvt_date.pl can be used for this.
If the MySQL table into which you want to import the Access data does
not exist, create it. The guess_table.pl utility
might be helpful at this point for generating a
CREATE TABLE statement.
Import the datafile into MySQL with LOAD
DATA or mysqlimport.
|
