Companies and organizations using Microsoft Access often struggle against its limitations that force them to find alternative solutions for data ware housing.One of the most popular way of extending capabilities of the database is MS Access to MySQL migration. MySQL provides all the necessary features to scale the database for the growing business requirements:
- Deployment of the data. When the data resides in MySQL, it is available to use from MS Access as well as from any other client applications compatible with ODBC. Also, MySQL is easily integrated with Web servers like Apache. The DBSM us supported by many languages, such as C++, Perl, PHP, Python, etc. In addition, MySQL database can be accessed through browsers on many types of OS, providing a platform-independent gateway to the data. All necessary components for building MySQL infrastructure can be obtained for free.
- Concurrent user access. Although MS Access provides some capabilities for data sharing, it is not powerful enough for corporate scale databases. On the other hand, MySQLcan handle many simultaneous users since it was initially designed to run in a multiple-user environment.
- Managing huge data. MySQL can process hundreds of gigabytes of data. Dare to try that with Microsoft Access?
- MS Access tables are stored on the local file system, so anyone having access to the Windows machine can gain access to the data. There is option of protecting database with a password, but it is not obliged and many users simply ignore it. MySQL server always manages the data security: anyone attempting to access the data must know MySQL user name and password.
- Unlike MS Access, MySQL can be obtained for free. Using the database through a Web interface can also reduce dependence on proprietary software and lower the software licensing cost.
Methods of Database Migration
In general, database migration from MS Access to MySQL is a combination of copying table structures, indexes, constraints and the data from the source database to the target. To continue using MS Access as interface to migrated database:
- delete MS Access tables
- establish an ODBC connection from MS Access to MySQL
- in MS Access database create links to the migrated MySQL tables
Some methods of database migration require ODBC connection to MySQL server. For this purpose, MySQL Connector/ODBC can be used.
Export Tables from MS Access
One approach to MS Access to MySQL database migration is to use the export feature provided by Microsoft Access to store the contents of each table into plain text format. Then each of those text files can be imported into MySQL via LOAD DATA statement. For example, table ‘mytable ’has been exported into a file ‘mytable.txt’ using comma separated values(CSV) format. Now the task is to import that file into table ‘mytable’ in a MySQL database ‘mydb’. This can be done via LOAD DATA statement running from mysql command line client:
C:\>mysql–u username –p mydb
mysql>LOAD DATA LOCAL INFILE ‘mytable.txt’
->INTO TABLE mytable
->FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘
->LINES TERMINATED BY ‘\r\n’;
This approach does not require any conversion tools and soit can be used even on machines having no MySQL support. In this case create the data files on MS Access machine, then copy them to another machine with MySQL support and load the data into MySQL as it is specified above. The disadvantage of this method is that the MySQL tables must already exist before importing the data, so you must run CREATE TABLE statements manually.
MS Access to MySQL Converters
There are special conversion tools can completely automate MS Access to MySQL database migration. Those tools create all MySQL tables as well as other important database objects (indexes, constraints, views) and load the data. This approach does not require any intermediate files and works either when direct connection to MySQL is available or not. For the second case MS Access to MySQL conversion tool may export the source database into SQL script containing statements to create all database objects and to load the data. That script can be imported into MySQL database later via any client software.
One of such tools is MS Access to MySQL converter offered by Intelligent Converters. It supports all versions of Microsoft Access and MySQL running on Linux/Unix/Windows platforms, as well as MariaDB and Percona forks and cloud variations of MySQL. Other features of MS Access to MySQL converter:
- filtering data for migration via SELECT-queries
- customizing structure of the resulting MySQL tables
- options to merge or synchronize existing MySQL database with MS Access data
- storing conversion settings into profile
- command line support