Most of public tutorial about database migration from MS Access to MySQL are focused on conversion of schemas and the data leaving queries outside of the scope. At the same,unlike the data MS Access queries are not processed by free conversion tools. So, the person responsible for MS Access to MySQL database migration either need to convert queries manually or purchase expensive software that can do this. This whitepaper contains ten most common recommendations on how to convert MS Access queries into MySQL views. It is expected that target audience has basic knowledge in database administration and syntax of SQL queries.
The first step is to extract queries from MS Access database in form of SQL statements. This can be done as follows:
1) Open the database in Microsoft Access and go to “Queries” tab.
2) Right-click on each query from the list and select “Design View” option.
3) Select “View” from menu and then “SQL View” option.
4) Copy and paste text of SQL statements into the output file.
Next step is to make the code of MS Access queries consistent with MySQL syntax. Below is the list of most popular Microsoft Access expressions and their equivalent in MySQL format:
1) convert text concatenation operators like ‘expr1 & expr2’ and ‘expr1 + expr2’ into ‘CONCAT (expr1, expr2)’, where expr1 and expr2 are values of text type
2) convert Microsoft Access logical constants into MySQL integer numbers (‘Yes’ becomes 1, ‘No’ becomes 0)
3) convert all date values from MS Access format #MM/DD/YY# into MySQL format ‘YYYY-MM-DD’
4) all function calls ‘Iif(condition, expr1, expr2)’ must be converted into ‘If(condition, expr1, expr2)’
5) convert all function calls ‘InStr(position, expr1, expr2)’ into MySQL equivalent ‘LOCATE(expr2, expr1, position)’
6) all function calls ‘NZ (expr1, expr1)’ must be converted into ‘IFNULL(expr1, expr1)’
7) MS Access function ‘date()’ that returns current date and time must be converted into MySQL equivalent ‘now()’
8) replace all occurrences of operator ‘<>’ by ‘!=’
9) Unlike MS Access, MySQL does not allow aliases in queries, so it is necessary to use the original column names or expressions always. For example, pattern
SELECT something as xyz, xyz+10…
must be converted into
SELECT something as xyz, something+10…
10) Not all MS Access functions have direct MySQL equivalents, for example First() and Last() functions. If the querying field has ascending sort order, First() function must be converted into min() and Last() – into max(). Otherwise, when it is possible to control the sort order, these functions can be converted as follows:
select colname from tabname order by something ASC LIMIT 1
and
select colname from tabname order by something DESC LIMIT 1
Note it is just a few of most frequent conversion rules listed above that must be implemented while converting MS Access queries into MySQL views. Large and complicated projects may require dedicated software solutions to automate migration of schemas, data, indexes, relationships between tables and queries.One of such products to migrate from Microsoft Access to MySQL can be found at https://www.convert-in.com/access-to-mysql.htm
