Category Archives: IERI

Moving from Microsoft Access to MySQL

We recently decided to make a public release of an old web-based application coded in ASP (classic, using VBScript) and using Microsoft Access. In order to make this application public we need to make a few modifications, not the least of which is moving from Microsoft Access to MySQL. Using Microsoft Access on the back-end would significantly hamper the ability of the application to support concurrent users, among other issues.

The majority of the coding modifications have yet to be made, but the database switch has already occurred. In the process of moving from MS Access to MySQL I discovered a few settings that would be helpful should this action need to be performed for other applications. These settings should enable similar applications to be moved with minimal modification to the programming.

First, let’s review some settings related to the MySQL ODBC driver. The settings are relevant to all versions of the driver, but the name of the setting may be different on different versions (I’m using 5.1.6). Here are the options which should be selected:

  • Return matched rows instead of affected rows
  • Treat BIGINT columns as INT columns
  • Enable safe options

The following information relates more generally to changes that may have to be made in the code:

  • MySQL doesn’t really support server-side cursors so the ODBC drivers fakes it. This is, mostly, fine except that some properties of the Recordset object are not available (namely RecordCount). In order to get full cursor support you should change the location from the server to the client (adUseClient or the literal value 3).
  • ASP doesn’t understand non-signed integers. This causes problems when performing operations using these values unless you manually type the value in your script, e.g. scriptvar = CInt(objrs("dbcol")). The other solution is to make all integers signed. Otherwise you will see the error: Variable uses an Automation type not supported in VBScript.
  • Related to the above is the usage of values from the database in comparison functions. VBScript variables are typed (e.g. integer, string, boolean, etc.). Though you can’t specify the type during variable instantiation (with Dim) VBScript does pay attention to type when performing comparisons. When two variables of different types are compared you will get a “Type mismatch” error. The resolution is to Ctype your variables if you run into this type of error.
  • Finally, check your SQL statements for any VBA function calls. These will either have to be modified into MySQL-compatible function calls or removed from the SQL code altogether.

There are a number of issues that may be encountered when attempting to convert an ASP-based application from MS Access to MySQL. The issues addressed here are only those relevant to this particular application. Other applications may require additional or different solutions and settings.

Resources: