Organizations that utilize MS Access for their business needs oftensufferfrom its limitations.This is primary reason for moving databasesto new system providing better performance and reliability such as MySQL. This article provides guidelines for database migrationfrom MS Access to MySQL.
Reasons of Migration
Replacement of Microsoft Access by MySQL as a database management systemleads tomultiple advantages.
Deployment. When using MySQL as data storageusers canstill work withMS Access as front-end, at the same time other possibilities exist as well. For example, particularusers can manage the data via standard MySQL client programs, othersmay work with custom GUI applications. MySQL is excellent choice for integrationwith 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 anoptional 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 TCOof administration and development for this database management system.
Methods ofMS Access to MySQL Migration
Basic approach to database migrationconsists ofextracting data from source, transforming it to comply with destination format and loading to the target database. There are multiple options to perform these operationsas 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 exportdata of each table as a text file. Then each of those files can be imported into MySQL using a LOADDATA statement or mysqlimporttool.
This optiondoes not requireany 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. Mainbottleneck 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 withmysqlconsole client or phpMyAdmin.
Learn more about other aspects of MS Access to MySQL migration at https://www.convert-in.com/access-to-mysql.htm