View Full Version : Slow page maintenance - Converting Access to MySQL

April 2nd, 2012, 09:28 AM
The rule of thumb for ColdFusion databases is never use Microsoft Access on production websites. In this post I will explain our current suggestion for converting a Microsoft Access database to a MySQL database for websites using ColdFusion however there are many other cases where this may be helpful such as with .NET, ASP or PHP websites that were originally developed to use Microsoft Access databases and have out grown them.

This post will not attempt to explain the reason for not using Microsoft Access for our Shared ColdFusion Hosting websites other than that at Hostek.com we want fast running servers which means happy customers. We have seen too many times that one Microsoft Access database can have a significantly negative impact on ColdFusion performance for many different reasons.

Download and install the Bullzip converter, you can get it here: http://www.bullzip.com/download.php
Create a MySQL database within your hosting control panel, make note of the server and login details.
We suggest making a "under construction" for the conversion.
Create a new DSN named "MyDSN_MYSQL" or delete the old and create a new DSN with the same name for your MySQL database.
Download the MS Access database you are about to convert to your local machine and start the Bullzip.
Follow the steps at - http://www.bullzip.com/products/a2m/doc/info.php when working with "Destination Database" section choose "Create Dump File".
Test before removing the construction page if possible and when comfortable, Go LIVE. See notes below for problem/solution.

Watch out for "#" symbols in MySQL database, Access apparently can use syntax like time="#00:00#" where MySQL will choke when encountering "#" symbol in a create table string (# can be safely removed in most cases).

When MS Access is converted to MYSQL non-null values can be set as 1. So a MS Access statement like

WHERE fieldname = YES

Will need to be changed to

WHERE fieldname = 1

Or a field that has value 1 will need to be updated and changed to YES and code will need to be updated to look like

WHERE fieldname = 'YES'