Convert MS Access queries to MySQL
|[MS Access to MySQL Converter] [About Migration] [Types Mapping]|
There is a lot of free tools and solutions to migrate Microsoft Access data to MySQL server. However, none of them can convert MS Access queries. So, everybody who wants to migrate complete database needs to convert queries manually. This article gives step-by-step instructions on how to convert queries from MS Access database into MySQL format. The target audience for this guide should have general database management knowledge and experience in composing SQL queries.
First step is to get MS Access queries in form of SQL statements. It can be done through the following steps:
Now it is necessary to make these SQL statements compatible with MySQL syntax. Below are the most important steps to convert Microsoft Access expressions into MySQL format.
1) replace all occurrenced 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 agruments only, that's why expressions like
expr1 & expr2 & expr3must be converted into
CONCAT(expr1, CONCAT(expr2, expr3))3) replace Microsoft Access boolean constants 'Yes' by b'1', 'No' by b'0'
4) MySQL cannot refer to aliases in SQL statements, so it is necessary to replace patterns like 'SELECT something as xxx, xxx+10...' by '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, first() function should be replaced by min() and last() - by max(). Otherwise, if it is possible to control default sort order, these functions can be replaced as follows:
select column_name from table_name order by something ASC LIMIT 1and
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)|
Of course, there are a lot of newances staying outside of this article, it just covers the most frequent issues of migrating Microsoft Access queries into MySQL views. If you need a solution for complete migration of MS Access database to MySQL server including data, indexes, foreign keys and queries, take a look at Access to MySQL converter