TechNote (Archived)

Using Microsoft Access Databases in a Production Environment

Overview

Microsoft Access is a desktop database, and not intended or designed for high-volume, multi-user use. Its performance under such conditions frequently becomes unpredictable and unstable. The simplicity of creating and maintaining databases in Access, and the facility of deployment entices many users to employ it for Web application serving in spite of its limitations. Even Microsoft recommends that Access be used"solely for development purposes and not for production". In short, there is no substitute for robust client/server database server products like Microsoft SQL Server, Oracle, Sybase, Informix and DB2 in anything but light-service Web applications.

Keeping these inherent limitations in mind, there are many different configurations for Microsoft Access, of varying robustness. The primary differentiator is the connection mechanism employed. ColdFusion natively supports the three primary connection mechanisms for Access connectivity. They are (in order of robustness):

OLE DB ColdFusion Administrator Configured (requires minimum of MDAC 2.1)
COM Objects (via the CFOBJECT tag)
ODBC ColdFusion Administrator or ODBC Control Panel (System DSN) Configured

Most ColdFusion users use ODBC connections to Access, but they have proven to be the least scalable and robust in internal load testing. Apart from different configuration of the data sources in the ColdFusion Administrator, OLE DB and ODBC connections to Access function programmatically identically. The reliability and robustness of OLE DB connections under load proved to be substantially greater than under ODBC. For this reason, it is advisable to use OLE DB connections to Access datasources instead of ODBC connections whenever possible.

Note:

OLE DB connections are not available in ColdFusion MX and higher.
OLE DB connections from ColdFusion are available in the Professional and Enterprise Editions of ColdFusion, but not in the Express Edition.
Any non-standard ANSI SQL syntax might cause error messages when switching from ODBC to OLE DB. You should test your application for any such errors before deploying it into a production environment.

Creating an OLE DB Data Source (ColdFusion 5 and earlier)

The following instructions assume that you are using the Microsoft OLE DB provider (driver). It is available from Microsoft's web site.

Install MDAC 2.1 (or higher) on your ColdFusion Server machine. MDAC stands for Microsoft Data Access Components and contains the ODBC/OLE DB subsystem as well as drivers for all Microsoft datatypes (Access, SQL Server, etc.). You can download the latest version from Microsoft. You can confirm that OLE DB drivers are installed by searching for "Microsoft.Jet.OLEDB" in the registry using REGEDIT.EXE if you find it, you likely have the proper driver installed.

Note: When installing MDAC, please stop ALL unnecessary services (webserver, virus scanner, mail server, etc.). This will ensure all the files are copied into place properly.
Open up the ColdFusion Administrator and select the OLE DB link from the green navigation bar on the left.
Enter a name for the new data source in the Data Source Name column, choose "Microsoft.Jet.OLEDB.3.51" (no matter which MDAC version you have) from the dropdown in the Provider column and click the Add button.
On the "Create OLEDB Interface Data Source" screen, enter in the relevant details for the new data source (Data Source Name, Database File, and Provider are all required fields). Data Source Name should already be filled in with whatever you specified in step 3. Provider varies based on the version installed. Database File should be the complete path and filename of the Access MDB file being used, including extension (e.g., the sample database included with ColdFusion might have the "Database File" ofc:\cfusion\database\cfexamples.mdb). DO NOT CLICK"CREATE" JUST YET.
Click on the "CF Settings >>" button to display further data source settings.
Enter a "Username" and "Password" for your database under the"ColdFusion Login." If your database does not have a Username and Password, then enter "Admin" as the "Username" without any password. YOU MUST ENTER AT LEAST A USERNAME HERE OR IT WILL NOT WORK.
Click the "Create" button to create the data source.
Confirm that the status of the new data source is "Verified." If the new data source indicates a status of "Failed," then review the above instructions.
Congratulations!! You may now use your OLE DB data source from ColdFusion code as if it were an ODBC data source.

Microsoft Access and ODBC

The Microsoft Access ODBC driver dynamically allocates as much memory as it needs in order to process a database request. In certain situations, however, the driver needs and attempts to allocate more memory than the maximum that it is allowed to use. When this occurs the driver can come to a halt, and stop responding to ColdFusion requests. ColdFusion then begins to pile up all of the subsequent incoming requests until it also uses up all of its resources available. Stopping and starting ColdFusion releases all of the connections to the database, which in turn releases all of the memory taken and resolves the problem momentarily.

ODBC and OLE DB Connectivity Tips

Regardless of whether you use OLE DB or ODBC connections, here are some additional steps that can improve your Access database connectivity:

On the Server Settings page in ColdFusion Administrator, setLimit Simultaneous Requests to 3-5 per processor.
In ColdFusion Administrator, modify every Access ODBC Data source and set the following (you will need to click the CF Settings button to see these):
- Buffer Size = 0 (ODBC only)
- Page Timeout = 600 (ODBC only)
- Uncheck Maintain Database Connections
Install the latest ODBC or OLE DB Drivers from Microsoft on your ColdFusion Server. You can find these drivers at www.microsoft.com/data/.
If needed, cycle the ColdFusion services each night using the \cfusion\bin\cycle.bat file.

Recognizing ODBC-Related Microsoft Access Connectivity Problems

The following errors are indicative of Microsoft Access ODBC connectivity problems:

Server has stopped: Unable to respond to request due to high load.
Request canceled or ignored by server Server busy or unable to fulfill request. The server is unable to fulfill your request due to extremely high traffic or an unexpected internal error. Please attempt your request again (if you are repeatedly unsuccessful you should notify the site administrator)

Following are the error messages displayed by the browser and in the error logs for each version of ColdFusion:

ColdFusion Server for Windows 95/98/NT 4.x (check the server.log):

Warning","TID=421","05/18/99","13:22:16","A request exceeded the timeout. The unresponsive thread count is up to 1."
"Fatal","TID=421","05/18/99","13:23:16","Unresponsive thread threshold reached. Restarting service."
"Error","TID=250","04/29/99","09:56:03","Windows NT error number 109 occurred."
"Error","TID=250","04/29/99","09:56:03","Windows NT error number 232 occurred."
"Error","TID=251","04/29/99","15:51:09","Error number 232 occurred while attempting to write the reply to the web server."
"Error","TID=149","02/26/99","11:23:40","Timed out while waiting for main thread to finish."

ColdFusion Server for Windows 95/98/NT 3.x (can be one of the following):

"Unable to retrieve the connection pool for the data source. Timed out waiting for data source. Unable to connect to data source name."
"Unable to get a connection to data source. Timed out waiting for data source. Unable to connect to data source name".
"Unable to delete the connection to data source. Timed out waiting for data source. Unable to connect to data source name".
"Unable to dispose of the connection pool. Timed out waiting for data source. Unable to connect to data source name."

ColdFusion Server for Windows 95/NT 2.0 and less:

"Error attempting to retrieve connection for data source the mutex was not signaled within the expected time period. Timed out waiting for data source. Unable to connect to data source name."