There is a lot of approaches to database migration from Microsoft Access to MySQL server. However, most of them are focused on converting table structures and data, while MS Access queries are out of the scope. That’s why everybody who wants to migrate complete database needs to convert queries manually or buy very expensive software that can automate it.
This article contains a few suggestions about converting MS Access database queries into MySQL views. Database management knowledge and experience in composing SQL queries are assumed for target audience of this guide.
First, it is necessary to extract queries in form of SQL statements from MS Access database. It can be done as follows:
1) Run Microsoft Access and open the source database. Go to “Queries” tab.
2) Open each query in Design View using the corresponding button or right-click on the query and select “Design View” option.
3) Select “View” item in the menu and choose “SQL View” option.
4) Copy the selected SQL statement to the Windows clipboard.
Now it is time to make SQL code of MS Access queries compatible with MySQL format. There are 10 tips on how to convert most popular Microsoft Access expressions according to MySQL syntax:
1) replace ‘%expr1% & %expr2%’ and ‘%expr1% + %expr2%’ by ‘CONCAT (%expr1%, %expr2%)’, where %expr1% and %expr2% are string expressions
2) replace date() by now()
3) replace all occurrenced of ‘<>’ by ‘!=’
4) replace InStr(%position%, %expr1%, %expr2%) by LOCATE(%expr2%, %expr1%, %position%)
5) 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…’
6) replace Microsoft Access boolean constants ‘Yes’ by b’1′, ‘No’ by b’0′
7) replace Iif(%condition%, %expr1%, %expr2%) by If(%condition%, %expr1%, %expr2%)
8) all date constants must be converted from #MM/DD/YY# to ‘YYYY-MM-DD’ format
9) replace ‘NZ (%expr1%, %expr1%)’ by ‘IFNULL(%expr1%, %expr1%)’
10) 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
That were the most frequent issues of migrating Microsoft Access queries into MySQL views. More tips and tricks for MS Access, MySQL and other popular databases can be found at:
http://www.convert-in.com/articles.htm