Migrating MS Access databases to MySQL

942

Although there are numerous tutorials on the Web that could guide you through migrating MS Access databases to MySQL server, they mainly describe conversion of the data while queries are not considered. On the other hand MS Access queries are not migrated into MySQL format by free conversion tools unlike the data. That’s why you either need to convert queries manually or buy expensive products that can handle it. Here you will find a number of suggestions how to modify text of queries while converting from MS Access to MySQL. Target audience of this tutorial must have basic knowledge in database administration and syntax of SQL queries.

Obviously, the first step is to extract MS Access queries as SQL statements 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 patterns like ‘%expr1% & %expr2%’ and ‘%expr1% + %expr2%’ into ‘CONCAT (%expr1%, %expr2%)’, where %expr1% and %expr2% are values of text type

2) convert Microsoft Access boolean constants into MySQL numbers (‘Yes’ => 1, ‘No’ => 0)

3) convert all date values from #MM/DD/YY# into ‘YYYY-MM-DD’ format

4) convert function calls ‘Iif(%condition%, %expr1%, %expr2%)’ into ‘If(%condition%, %expr1%, %expr2%)’

5) convert function calls ‘InStr(%position%, %expr1%, %expr2%)’ into ‘LOCATE(%expr2%, %expr1%, %position%)’

6) convert function calls ‘NZ (%expr1%, %expr1%)’ into ‘IFNULL(%expr1%, %expr1%)’

7) convert function calls ‘date()’ into ‘now()’

8) replace all occurrences of ‘<>’ by ‘!=’

9) MySQL does not accept aliases in queries, so it is necessary to use the original expression always. For example, pattern like ‘SELECT something as xyz, xyz+10…’ must be converted into ‘SELECT something as xyz, something+10…’

10) MySQL does not have direct equivalents of First() and Last() MS Access 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 column_name from table_name order by something ASC LIMIT 1

and

select column_name from table_name order by something DESC LIMIT 1

Of course, it is just a few of all possible conversions that must be done while transferring views from MS Access to MySQL. In case you need a complex solution for migrating Microsoft Access database to MySQL server including data, indexes, foreign keys and queries, you should evaluate this product: http://www.convert-in.com/acc2sql.htm


Comments are closed.