Convert Access to MySQL
Companies that use Access eventually start struggling with its restrictions. Right now they come with an alternative: transfer data repositories into a system that offers better performance and consistency and that’s more flexible in the way it allows information to be utilized. MySQL offers the features you will require to follow this program.
In this article you will see why you should think about using MySQL rather than Access for data management, and offers guidelines for transferring information out of Access and into MySQL as you prepare to really make the switch.
Why Convert Access to MySQL
Application of MySQL as a storage manager for Access offers several advantages. The first is that you can use your data in additional ways when it is not locked into Access. Other differences pertain specifically in the case that you plan to keep using Access as the user interface for your information. Here are the key reasons to convert Access to MySQL:
Deployment of data. If your information resides in MySQL, you’re at liberty to keep using it from Access if you want, however a lots of other possibilities open up at the same time. Any type of MySQL client can make use of the data, not only Access. For instance, other individuals can make use of the info within the standard MySQL client programs or from GUI-based applications. Your database also gets more accessible over the internet. MySQL integrates easily with Web servers like Apache through any of a number of languages, including Perl, PHP, Python, etc. This enables you to offer a Web interface in your database with all the language which you decide on. Furthermore, the interface could be accessed by browsers on various types of machines, providing a platform-independent entryway to your information. Every one of these components can be found at no cost.
Multiple-user access. Although Access provides some data sharing capabilities, that isn’t really its strength. It has the feel of a single-user data manager created for local use. MySQL, alternatively, easily handles many simultaneous users. It was developed from the ground-up to operate in a networked environment and also to be considered a multiple-user system that’s very effective at servicing large numbers of clients.
Dealing with large databases. MySQL can manage numerous megabytes of data, and much more. Wish to try that with Access?
Security. When Access tables are stored locally, you can now walk close to your Windows machine, launch Access, and obtain your tables. It’s simple to assign a database password strength, however, many people routinely fail to do so. Whenever your tables are saved in MySQL, the MySQL server manages security. Anyone trying to access your information should know the appropriate username and password to connect to MySQL.
Backup management. When you work with a company that supports many users, decision to convert Access to MySQL offers a benefit for backups and data integrity. With Access databases centralized in MySQL, they are all supported with the regular MySQL backup processes that already exist in your site. If individual Access users each store their data locally, backup could be more difficult: 50 users means 50 database backups. While many sites address this issue with the use of network backups, others contend with it simply by making backups the task of individual machine owners–which however sometimes means no backups at all.
Local disk storage requirements. Local Access database files become smaller, simply because the items in tables aren’t stored internally, they’re stored as links in the MySQL server where tables reside. This leads to reduced local disk usage. And, if you want to disperse a database, less information needs to be copied. (Obviously, anyone you distribute the database to also will need to have easy access in the MySQL server.)
Cost. MySQL can be acquired at no cost. Access cannot. Offering other ways of making use of your database (including through a Web interface) can help to eliminate your reliance on proprietary software and minimize your software acquisition and licensing costs.
Hardware choices. MySQL operates on several platforms; Access is a single-platform application. If you need to use Access, your available choice of hardware is decided for you.
Ways to Convert Access to MySQL
To convert Access to MySQL you first of all copy the information of your tables from an Access database to the MySQL server. To execute the whole process of moving the tables to MySQL, you can pick right from several techniques, described below. If you intend to carry on using Access for the interface to your data, the next thing after transferring the tables will be to replace all of them with links: Delete the tables saved in your Access database, establish an ODBC connection from Access to the MySQL server, and recreate the tables as links to the MySQL tables. Unless you don’t intend to keep using Access, you don’t need to create any links.
Some transfer methods require making an ODBC link with the MySQL server. Just for this you can use MySQL Connector/ODBC, the MySQL-specific ODBC driver.
Telling Microsoft Access to Export Its Unique Tables
One method to convert Access to MySQL is by using the export feature supplied by Access itself to write out the contents of each table as a text file. Each file then could be loaded into MySQL by using a LOAD DATA statement or even the mysqlimport command-line utility. Assume that you export a table mytable right 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 such as this:
C:\> mysql mydb
mysql> LOAD DATA LOCAL INFILE ‘mytable.txt’
-> INTO TABLE mytable
-> FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘
-> LINES TERMINATED BY ‘\r\n’;
On the other hand, use mysqlimport in the command line (type the command all on one line):
C:\> mysqlimport –local –fields-terminated-by=,
Whenever you provide connection parameters including the hostname, username, or password, list them on the mysql or mysqlimport command line before the database name:
C:\> mysqlimport –local –fields-terminated-by=,
-h some_host -p -u some_user
The benefit of this option to convert Access to MySQL is that it requires no special conversion tools. It can be utilized to create data file types even on machines that possess no MySQL support. (If you don’t have the MySQL client programs set up on your Access machine, create the data files, then copy them all to another machine where the MySQL programs are installed and load the files into MySQL from there.) The downside is the fact that MySQL tables must already exist before you can load data into them, therefore you must issue the correct CREATE TABLE statements yourself. For the example just shown, meaning you have to already have set up the table mytable in the mydb database before using either LOAD DATA or mysqlimport.
Convert Access to MySQL via Generating Intermediate Files
This approach to convert Access to MySQL requires a product or tool that reads Microsoft Access table and converts it into one or more files containing SQL statements that create destination MySQL table and load data into it. Then you execute the intermediate SQL file or files with the mysql program. Several free converters that actually work this way can be found, because both versions takes the form of an Access module:
Works together with all available versions of Microsoft Access. Exports all tables in a database, producing one file containing DROP TABLE statements (for those who like to take out MySQL tables created during an earlier data transfer exercise) and the other file containing CREATE TABLE and INSERT statements for creating and loading the tables. The files are written to the C:\TEMP directory.
Exports all tables in a database into a file C:\TEMP\mysqldump.txt containing DROP TABLE, CREATE TABLE, and INSERT statements to drop any current MySQL tables and recreate them. Less sophisticated than exportsql.txt when it comes to type conversion and handling of special characters.
Access97 only. Exports selected tables to files in a directory which you decide on. Writes a data file for each selected table, plus one SQL script containing CREATE TABLE statements for creating the tables and LOAD DATA statements for importing the data files into them.
Immediately after adopting the export technique of a converter that generates intermediate SQL files from Access tables, you’ll end up getting more than one files that ought to be executed while using the mysql program, as follows. Let’s assume that you need to create tables in a database named mydb, it is possible to carry out a SQL file file.sql such as this:
C:\> mysql mydb < file.sql
Whenever you offer connection parameters, list them on the command line before the database name:
C:\> mysql -h some_host -p -u some_user mydb < file.sql
Converters That Perform Direct Transfer Of Data
A lot of software tools can convert Access to MySQL directly. That’s, they create the MySQL tables to suit your needs and load the data into them also. This avoids the advantages of any intermediate files. Alternatively, such tools require that you will be able to connect with the MySQL server in the machine which your Access data is stored. This criteria is definitely satisfied if you happen to install MySQL on your own Access machine.
One among such tools is MS Access to MySQL database converter developed by Intelligent Converters. It supports all versions of Microsoft Access and MySQL running on Linux/Unix/Windows platforms. The software allows to filter data for conversion via SELECT-queries, to modify structure of the resulting MySQL tables and also to synchronize MySQL database with MS Access data.