There are multiple free tools and solutions to migrate Microsoft Access data to MySQL server. However, there is one drawback but none of them can convert MS Access queries. So, anyone who intends to migrate complete database has to convert queries manually. This article is about step-by-step instructions on how to convert queries from MS Access database into MySQL format.
Please note that the target audience for this guide should have general database management knowledge and experience in composing SQL queries.
First step is to convert MS Access queries in the form of SQL statements through the following simple steps:
The next step is to make these SQL statements compatible with MySQL syntax.
Below are the most important steps to convert Microsoft Access expressions into MySQL format.
1) Substitute all occurrence of ‘<>’ by ‘!=’
2) Replace ‘expr1 & expr2’ and ‘expr1 + expr2’ by ‘CONCAT (expr1, expr2)’, where expr1 and expr2 are string expressions. Note that MySQL CONCAT accepts 2 arguments only, that’s why expressions like
Access: expr1 & expr2 & expr3
must be converted into
MySQL: CONCAT(expr1, CONCAT(expr2, expr3))
3) Substitute Microsoft Access Boolean constants:
4) MySQL cannot refer to aliases in SQL statements, so it is necessary to replace patterns like
Access: ‘SELECT something as xxx, xxx+10…’ by
MySQL: ‘SELECT something as xxx, something+10…’
5) There is no direct equivalent of First() and Last() aggregate functions in MySQL. If the querying field has ascending sort order:
Otherwise, if it is possible to control default sort order, these functions of Microsoft Access can be replaced as follows:
select column_name from table_name order by something ASC LIMIT 1
select column_name from table_name order by something DESC LIMIT 1
6) All date constants must be converted from #MM/DD/YY# to ‘YYYY-MM-DD’ format
7) Replace MS Access built-in functions by MySQL equivalents using the following table:
|iif(condition, expr1, expr2)||if(condition, expr1, expr2)|
|InStr(position, expr1, expr2)||locate(expr2, expr1, position)|
|nz(expr1, expr2)||ifnull(expr1, expr2)|
This article just covers the most frequent issues of migrating Microsoft Access queries into MySQL views. If you are looking for a solution for complete migration of MS Access database to MySQL server including indexes, foreign keys and queries, take a look at Access to MySQL converter