Convert MS Access queries to MySQL

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.

Step 1:

First step is to convert MS Access queries in the form of SQL statements through the following simple 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” button on the keyboard to copy the selected SQL statement to the Windows clipboard

Step 2:

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:

  • ‘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

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:

  • first() function should be replaced by min()
  • last() function should be replaced by max()

Otherwise, if it is possible to control default sort order, these functions of Microsoft Access can be replaced as follows:

  • first() function is replaced by MySQL statement

select column_name from table_name order by something ASC LIMIT 1

  • last() function is replaced by MySQL statement

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

 

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

Dean@techsophist.net'