Paul DuBois
paul@kitebird.com
Document revision: 1.01
Last update: 2003-01-25
Businesses that use Access eventually begin to struggle against
its limitations. Now they have an alternative: Move data repositories
to a system that provides better performance and reliability and
that is more flexible in how it allows information to be used.
MySQL provides the features you need to follow this course. MySQL
includes a business strength database server that runs under either
Windows, Linux, or Unix. MySQL features InnoDB, the table type
that provides performance and reliability enhancements such as
row-level locking, transaction support, and automatic crash recovery.
By using MySQL as a data management platform, you can continue
to manipulate your data through Access as a front end if you wish.
But you can also exploit your data in other ways, for example,
by using MySQL to help you establish or strengthen your web presence.
To do this, deploy a web server that handles requests from people
who visit your site and tie it to MySQL using a web programming
language. A popular combination employs the Apache web server
and the Perl and PHP programming languages. Perl and PHP both
are extremely popular for web programming, and both interface
easily with MySQL.
The following article explores reasons to consider using MySQL
instead of Access for data management, and provides guidelines
for transferring information out of Access and into MySQL when
you're ready to make the switch.
Microsoft Access or MySQL?
Microsoft Access is a popular data management application that
allows you to store information in tables that it manages directly
from the local disk. You can also use Access as a front end, that
is, as an interface to information that is located elsewhere and
handled by another storage management system. In this case, Access
acts as a client that connects to a server that provides the data.
The MySQL database system is one such storage manager; if you
install the MySQL Connector/ODBC driver (formerly known as MyODBC),
Access can make ODBC connections to MySQL servers over the network.
You can still use the contents of your tables through Access,
but the tables themselves are hosted by the MySQL server.
Access has its strengths, such as an easy to use interface. Access
also has its limitations--it's generally used as a personal or
single-user application, typically for managing limited amounts
of data. (Access is not commonly used for databases hundreds of
megabytes in size, for example.) Because of its storage management
limitations, you may be considering how to retain the Access interface
but migrate your information to a storage manager with greater
capabilities. Or you may even be considering a move away from
Access entirely. This article outlines some benefits that you
stand to gain by using MySQL to manage your data, and provides
some guidelines to help you migrate locally stored Access tables
to MySQL. The final section of the article lists links to locations
where you can find the tools discussed here.
Reasons to Migrate from Microsoft Access to MySQL
Use of MySQL as a storage manager for Access offers several benefits.
One is that you can use your information in additional ways when
it's not locked into Access. Other differences pertain more specifically
to the case where you intend to continue using Access as the user
interface to your information.
Deployment of information. When your information resides
in MySQL, you're free to continue using it from Access if you
wish, but a number of other possibilities open up as well. Any
kind of MySQL client can use the information, not just Access.
This allows your data to be exploited more fully in more contexts,
and by more people. For example, other people can use the data
through the standard MySQL client programs or from GUI-based applications.
Your database also becomes more accessible over the Web. Access
now provides some capabilities for making a database available
on the Web, but if MySQL manages the database, you have a wider
range of options. MySQL integrates easily with Web servers like
Apache through any of a number of languages, such as Perl, PHP,
Python, Java, and Ruby. This allows you to provide a Web interface
to your database with the language of your choice. In addition,
the interface can be accessed by browsers on many types of machines,
providing a platform-independent entryway to your information.
All of these components can be obtained for free--MySQL, Apache,
and the languages just mentioned have been released as Open Source.
You can also obtain them in packages that include support.
Multiple-user access. Although Access provides some data
sharing capabilities, that is not really its strength. It has
the feel of a single-user data manager designed for local use.
MySQL, on the other hand, easily handles many simultaneous users.
It was designed from the ground up to run in a networked environment
and to be a multiple-user system that is capable of servicing
large numbers of clients.
Management of large databases. MySQL can manage hundreds
of megabytes of data, and more. Care to try that with Access?
Security. When Access tables are stored locally, anyone
can walk up to your Windows machine, launch Access, and gain access
to your tables. It's possible to assign a database a password,
but many people routinely neglect to do so. When your tables are
stored in MySQL, the MySQL server manages security. Anyone attempting
to access your data must know the proper user name and password
for connecting to MySQL.
Backup management. If you work in an organization that
supports many Access users, migrating data to MySQL provides a
benefit for backups and data integrity. With Access databases
centralized in MySQL, they're all backed up using the regular
MySQL backup procedures that already exist at your site. If individual
Access users each store their data locally, backup can be more
complicated: 50 users means 50 database backups. While some sites
address this problem through the use of network backups, others
deal with it by making backups the responsibility of individual
machine owners--which unfortunately sometimes means no backups
at all.
Local disk storage requirements. Local Access database
files become smaller, because the contents of tables are not stored
internally, they're stored as links to the MySQL server where
the tables reside. This results in reduced local disk usage. And,
should you wish to distribute a database, less information need
be copied. (Of course, anyone you distribute the database to also
must have access to the MySQL server.)
Cost. MySQL can be obtained for free. Access cannot. Providing
other means of using your database (such as through a Web interface)
can reduce your dependence on proprietary software and lower your
software acquisition and licensing costs.
Hardware choices. MySQL runs on several platforms; Access
is a single-platform application. If you want to use Access, your
choice of hardware is determined for you.
Migration Strategies
Should you wish to migrate from Access to MySQL, you can do so
either partially or completely. It's not without reason that Access
is popular--it provides an interface that many people are comfortable
working with. If you're such a user, you can continue to use the
interface by migrating partially: Transfer locally stored Access
tables to MySQL, then set up links in the Access database that
point to the tables managed by MySQL server. This way you continue
to enjoy the familiarity of the Access interface (the tool with
which you're conversant), but also take advantage of the strengths
of MySQL for data storage, management, and security.
If you're less tied to the user interface, you can migrate completely
away from Access. Transfer your Access tables to MySQL, then use
your information with tools intended for working with MySQL.
Methods of Transferring Databases from Microsoft Access to MySQL
In general, to migrate information from Access to MySQL, you first
copy the contents of your tables from an Access database to the
MySQL server. (To perform the operation of transferring the tables
to MySQL, you can choose from several methods, described below.)
If you plan to continue using Access for the interface to your
data, the next step after transferring the tables is to replace
them with links: Delete the tables stored in your Access database,
establish an ODBC connection from Access to the MySQL server,
and recreate the tables as links to the MySQL tables. (Naturally,
before you delete anything, it's prudent to make a backup first,
just in case something goes wrong.) If you don't plan to continue
using Access, you need not create any links.
Some transfer methods require making an ODBC connection to the
MySQL server. For this you can use MySQL Connector/ODBC, the MySQL-specific
ODBC driver.
Telling Microsoft Access to Export Its Own Tables
One approach to migrating data from Access to MySQL is to use
the export feature provided by Access itself to write out the
contents of each table as a text file. Each file then can be loaded
into MySQL using a LOAD DATA statement or the
mysqlimport command-line utility. Suppose you export a
table mytable into a file mytable.txt using CSV
(comma separated values) format, and you want to import it into
a table named mytable in a MySQL database named mydb.
You can invoke the mysql program, then issue a LOAD
DATA statement to import the file like this:
C:\> mysql mydb
mysql> LOAD DATA LOCAL INFILE 'mytable.txt'
-> INTO TABLE mytable
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';
Alternatively, use mysqlimport from the command line (type
the command all on one line):
C:\> mysqlimport --local --fields-terminated-by=,
--fields-enclosed-by='"'
--lines-terminated-by='\r\n'
mydb mytable.txt
If you need to provide connection parameters such as the hostname,
user name, or password, list them on the mysql or mysqlimport
command line before the database name:
C:\> mysqlimport --local --fields-terminated-by=,
--fields-enclosed-by='"'
--lines-terminated-by='\r\n'
-h some_host -p -u some_user
mydb mytable.txt
The advantage of this approach is that it requires no special
conversion tools. It can be used to produce data files even on
machines that have no MySQL support. (If you don't have the MySQL
client programs installed on your Access machine, create the data
files, then copy them to another machine where the MySQL programs
are installed and load the files into MySQL from there.) The disadvantage
is that the MySQL tables must already exist before you can load
data into them, so you must issue the appropriate CREATE
TABLE statements yourself. For the example just shown,
that means you must already have created the table mytable
in the mydb database before using either LOAD
DATA or mysqlimport.
A second approach to data transfer is to use a converter that
reads an Access table and produces from it one or more files containing
SQL statements that create the table for you and load data into
it. Then you execute the intermediate SQL file or files using
the mysql program. Several free converters that work like
this are available, each of which takes the form of an Access
module:
C:\> mkdir C:\TEMPAlternatively, you can modify the module source so that it writes files to another existing directory.
C:\> mysql mydb < file.sqlIf you need to provide connection parameters, list them on the command line before the database name:
C:\> mysql -h some_host -p -u some_user mydb < file.sql
Some conversion tools can transfer data directly from an Access
database into MySQL. That is, they create the MySQL tables for
you and load the information into them as well. This avoids the
need for any intermediate files. On the other hand, such tools
require that you be able to connect to the MySQL server from the
machine on which your Access information is stored. (This requirement
is easily satisfied if you install MySQL on your Access machine.)
Tools that can perform direct data transfer are:
If you want to transfer Access2000 databases, you need to enable
DAO. (If you don't, DBTools will crash whenever you try to open
an Access2000 database.) To turn on DAO, launch DBTools (it will
tell you there is no server profile; that's normal), select the
Options >> Preferences menu item, and select the DAO 3.6
option. Then quit and relaunch DBTools, because DAO isn't actually
activated until the next launch after you enable it.
With DBTools running, establish a connection to your MySQL server.
(Click the Server icon in the toolbar or use the Server >>
Add Server menu item to define a profile for the MySQL server
you want to connect to.) You must be connected to the server before
you can transfer information; many of the menu items and icons
in the tool bar are disabled until you establish a connection,
including those related to importing data.
After connecting to MySQL, use the Import Data Wizard to select
the Access database file containing the tables you want to transfer.
One of the dialogs presented during this process asks you to select
the file type for the kind of database you want to use. Select
the Access97 type for either Access97 or Access2000 databases.
If you intend to continue using Access after transferring the
tables, open the database from Access, delete the tables that
you just transferred to MySQL, connect to the MySQL server, and
set up links to the tables.
Conclusion
This article lays out some of the reasons you may find it advantageous
to switch from storing and managing data using Access, and to
use MySQL instead. These include multiple-user availability, security,
and ability to manage large amounts of information. The article
also describes some converters you can use to migrate your existing
Access data into MySQL. If you're ready to move beyond the limitations
of Access, MySQL provides the features you need.
Resources
MySQL and MySQL Connector/ODBC, the MySQL driver for ODBC, are
available from the MYSQL AB web site at http://www.mysql.com/.
Information about Microsoft Access may be obtained at: http://www.microsoft.com/office/access/.
Links for the conversion tools discussed in this article are available
at the Contributed Software page of the MySQL Web site (http://www.mysql.com/doc/en/Contrib.html).
Some of the converters have their own pages as well. These may
provide versions that are more up-to-date than those available
on the MySQL Web site:
The original version of this article was written for NuSphere
Corporation. The current version is an updated revision of the
original.