While Microsoft Access remains one of the most well-known data management systems, with its ease of use in terms of software development tools, and its interface, which is popular for its user-friendliness, it does have a number of limitations, especially when using it to create larger databases for more users. Its success in storing structured data, when configured for small groups of users, in local file systems, is widely lauded, but when expanding the scale one may hit its limits relatively quickly, with a max of 255 users (which is way more than the recommended 100 concurrent users anyway), and a file size cap of 2 GB. With this in mind, many companies go the route of migrating databases from MS Access to MySQL when expanding.

While the benefits of such a move may initially seem obvious, in reality it can be a daunting and labor-intensive task. Yes, MySQL beats MS Access in terms of numbers of concurrent users, much improved security, and cross plat forming, along with an array of other benefits, but one really should closely analyze one’s business needs and numbers and assess whether one does actually need to make such an upgrade. 

Of course, if one does decide to migrate one’s database, there are generally a number of steps which are followed when moving things over to MySQL from MS Access. Some of these can be automated, especially when you’re worried about data loss or corruption. Usually one begins with converting DDL SQL statements, which contain constraint definitions and the MS Access table, into MySQL format. These are then loaded onto the MySQL server. After this, one needs to create comma-separated values files, separated into a single file per table. These files contain data which you must export from the MS Access table. These .csv files are then each converted and imported to the MySQL database. More exporting also needs to be done: the MS Access queries must be exported into MySQL format, and then these too will be loaded into the database.  

While this can be a lot of work, the fact that some of it can be automated does help, and other factors, such as the fact that MS Access is able to export data to a number of different formats helps too. In fact, because of this ability to export to other formats, like the abstract ODBC source, one can follow a certain procedure to use the ODBC connector, or another appropriate driver, to export the MS Access table.

To do this, you need to first open the MS Access database. Choose a table to export, and click on it, and then go to the ‘file’ tab and choose ‘export’. This will open a dialog, headed ‘Export Object Type’, and in this you should select ‘ODBC Database’ to proceed. While this will load the MS Access data to the MySQL database, it will not convert certain constraints, and won’t convert queries.    

One other way to successfully migrate data is with the use of a software tool tailored to this purpose. Intelligent Converters has made an application for this specific purpose, called Access-to-MySQL. In this tool, all variations (including Percona and MariaDB) and versions of MySQL and MS Access are supported, and in addition if you use this tool you can use the converter’s command line to automate, schedule and also script the entire process of the conversion.

Access to MySQL boasts a number of features: it is able to handle any relationship between tables, and of course all indexes, and is compatible with all MS Access types and attributes, which when performing a transformation it will hand in an intelligent manner, and it is also able to convert queries from MS Access into MySQL views. It has the capability to migrate any size MS Access database, from simple and small to extremely large and complex, with a performance average of 2000 records p/s. The interface is especially easy to use, and Unicode is supported. As an added bonus, every purchase of the product comes with a one year update subscription, in which free new versions will be provided, and of course unlimited support is also included for a year after date of purchase.