Organizations that utilize MS Access for their business needs often suffer from its limitations. This is primary reason for moving databases to new system providing better performance and reliability such as MySQL. This article provides guidelines for database migration from MS Access to MySQL.
Reasons of Migration
Replacement of Microsoft Access by MySQL as a database management system leads to multiple advantages.
Deployment. When using MySQL as data storage users can still work with MS Access as front-end, at the same time other possibilities exist as well. For example, particular users can manage the data via standard MySQL client programs, others may work with custom GUI applications. MySQL is excellent choice for integration with Web servers like Apache. Developers can work with it on any modern script languages such as Perl, PHP, Python, etc. The interface can be accessed through web browsers that provides platform-independent gateway to the data stored in MySQL. And best of all – these components are free.
Multiple-user access. MS Access provides some capabilities of data sharing on file access level, however it has been primary designed for local use. Being a true client-server application MySQL easily handles many simultaneous users.
Security. Since MS Access tables are stored locally, anyone can access to the data. There is an optional password for database, but it looks like a weak attempt of providing illusion of security. MySQL server manages security according traditional concept of permissions and roles for relational DBMS.
Cost. MySQL is distributed under open source license while MS Access is a commercial application. There are also many free tools for MySQL that can essentially reduce TCO of administration and development for this database management system.
How to Migrate from MS Access to MySQL
Basic approach to database migration consists of extracting data from source, transforming it to comply with destination format and loading to the target database. There are multiple options to perform these operations as it is described below. Some methods require ODBC connection to the MySQL server, MySQL Connector/ODBC driver may be used for this purpose.
Use Microsoft Access to Export Data
The most straight forward approach to database migration from MS Access to MySQL is using the export feature provided by MS Access itself to export data of each table as a text file. Then each of those files can be imported into MySQL using a LOAD DATA statement or mysqlimport tool.
This option does not require any special conversion tools and even MySQL. If there is no MySQL client installed on the same machine with MS Access, just create the data files and transfer it to MySQL machine. Main bottleneck of this method is that MySQL tables must be manually created before loading data.
Generate MySQL Scripts
The second approach to MS Access to MySQL database migration is to use script or tool that reads source tables and converts it into one or more MySQL script files. These scripts contain SQL statements to create tables and fill them with the appropriate data. Database administrator will be able to import script files to MySQL server with mysql console client or phpMyAdmin.
Learn more about other aspects of MS Access to MySQL migration at https://www.convert-in.com/access-to-mysql.htm