Microsoft Access
or the MySQL database?
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 MyODBC driver, Access can make ODBC connections to
MySQL database 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 the MySQL
database to manage your data, and pro-vides 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 the MySQL database
The use of the MySQL database
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 database 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.
Microsoft 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. The MySQL database integrates easily with Web
servers like
Apache through any of a
number of languages, such as Perl, PHP, ASP, Java. 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—the MySQL database,
Apache web server, 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's
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. The MySQL database 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 a MySQL database, the MySQL server manages
security. Anyone attempting to access your data must know the proper
user name and password for connecting to the MySQL database.
Backup management. If
you work in an organization that supports many Access users, migrating
data to the MySQL database 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 database 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; Microsoft 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 the MySQL database, you can do so either partially or
completely. It's not without reason that Microsoft 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
the MySQL database 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 the MySQL database.
Methods of Transferring
Databases from Microsoft Access to the MySQL database
In general, to migrate information
from Microsoft Access to the MySQL database, 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 database server.
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 database support. (If
you don't have the MySQL database 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 database 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.
Connecting
MS Access to a MySQL database
ON LOCAL PC:
1) Download and Install MyODBC 2.50.19 (or most current version) on
local
Win95 machine with MS Access installed
--can download at: http://www.mysql.com/download.html
2) Fill in the following settings:
Windows DNS Name: You can choose the names, must be unique
Server: This is your domain name or IP address
MySQL Database Name: The name of your MySQL database
User: Your MySQL username
Password: Your MySQL password
Port: leave blank for default (3306(
Options; Select "Return Matching Rows"
3) To Link a Table
a) File...Get External Data...Link Tables
b) Under 'Files of Type:', select "ODBC Database"
c) Select Machine Data Source Tab, and select the appropriate Data Source
Name
d) Select the tables(s) to link
