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 with 100% accuracy. So, everyone who needs complete database migration from MS Access to MySQL, has to manually convert or at least fine-tune automated conversion of queries. 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) convert MS Access built-in functions into MySQL equivalents using this table

Of course, there are a lot of nuances 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 MS Access to MySQL converter