Home > Documentation > MS Access to MySQL

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:

  1. Start Microsoft Access and open the database containing the query that is to be converted to MySQL
  2. Open the selected query in Design View using the "Design View" button or right-click on the query and select "Design View".
  3. Select "View" from the menu and select "SQL View"
  4. Press "Ctrl"+"C" buttons on the keyboard to copy the selected SQL statement to the Windows clipboard

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 & expr3
must 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 1
and
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:

Microsoft Access MySQL
asc ascii
chr char
date() now()
iif(condition, expr1, expr2) if(condition, expr1, expr2)
InStr(position, expr1, expr2) locate(expr2, expr1, position)
int floor
lcase lower
lcase$ lower
len lenth
ltrim$ ltrim
nz(expr1, expr2) ifnull(expr1, expr2)
rtrim$ rtrim
sgn sign
ucase upper
ucase$ upper

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 indexes, foreign keys and queries, take a look at Access to MySQL converter