From MS Access to MySQL

Published: Thursday, August 21, 2014

Tags: MySQL web

The mitigation is supposed to be easy, MySQL has provided an official migration guide, as well as the ODBC guide.

However, I did not have much luck making these work. Main reasons were 1) The original MS database was 32-bit, but now I only have access to 64-bit PCs; 2) There are Unicode characters in the database; and 3) The date format does not match.

I ended up using the old ASP + MS Access to generate XML with UTF-8 character set, and use PHP XMLReader to parse the XML, and then write to MySQL database. Remember to use CDATA section for contents with illegal XML element such as "<" and "&".

Another important consideration is the script execution time. The default limit is 30 seconds, which may not be long enough. Simply use set_time_limit(int $seconds) to reset the timeout counter.

The date format mismatch can also be solved inside PHP, using date() and strtotime() functions:

$date_new = date("Y-m-d H:i:s",strtotime($date_old));