Who should read this article: Anyone hitting an Access database from ASP

New: Microsoft Jet is deprecated. Microsoft's current recommendation is to not use Jet at all. "No new feature enhancements will be made to Jet... It is highly recommended that while developing new applications, avoid using these [deprecated] components. Additionally, while upgrading or modifying existing applications, remove any dependency on these components" (Source: Microsoft's MDAC Road Map, under "Deprecated Components"). I assume the intention here is NOT to make everyone who used Access shell out thousands of dollars more for SQL Server, but rather to have Access users create MSDE projects (new as of Access 2000) instead of Jet databases. Find out more about MSDE here.

Let me start off by saying that Access databases were never meant to be hit from the web. In Microsoft's words, "While Microsoft Jet is consciously (and continually) updated with many quality, functional, and performance improvements, it was not intended (or architected)... to be used with high-stress, high-concurrency, 24x7 server applications, such as web, commerce, transactional, messaging servers, and so on" (Source: Microsoft KB article Q222135).

That said, they work fairly well under LIGHT load circumstances, and are disturbingly popular. I highly recommend designing your site starting out using SQL Server or Oracle. If you can't, but are planning to move to SQL Server later, go for MSDE.

If you insist on using Access databases from the web, here are some things you should know.

What's Microsoft Jet? Is it the same thing as Access?

No. Microsoft Jet is a database engine. Microsoft Access is a GUI that allows you to visually examine and modify the database. Microsoft Access uses Microsoft Jet behind the scenes, hiding all the ugly programming details behind the user interface we all know and love. If you're familiar with Microsoft SQL Server, a parallel would be that SQL Server is the database engine, and the Enterprise Manager is the GUI.

Technically speaking, you're using a Jet database on the server, not an Access database. But technically speaking, the millennium didn't begin until 2001, but that didn't stop people from calling the year 2000 the "new millennium." You'll see me use the terms interchangeably. Even Microsoft doesn't always differentiate--the ODBC driver is called the "Microsoft Access Driver."

Make sure you have adequate permissions on the database

To access the database, the web user--by default, IUSR_[MachineName] for ASP applications (not IWAM_[MachineName], even if the app is running in a separate memory space), and ASPNET for ASP.NET applications--must have at least the following permissions on the directory containing the Access .MDB database file:

Special Directory Access:
Write - Allows adding files and subdirectories. This is to create the .LDB locking file.

Special File Access:
Read - Allows viewing the file's data. This is for SELECT queries and other common database operations.
Write - Allows changing the file's data. This is for INSERT, UPDATE, and DELETE queries, as well as SQL DDL statements (CREATE, ALTER, DROP).
Delete - Allows deleting the file. This is to delete the .LDB locking file when the web user has finished accessing the database.

Most permission problems I've seen can be solved by checking the database directory. However, the Jet engine needs to create some temporary files (such as those named JET*.tmp), and it seems to need your TEMP and WINNT directories to do so. My testing of these directories hasn't been as extensive, but you can find information on these requirements in Microsoft KB articles Q251254 and Q164535. This is another potential source of "Unspecified error", even when using the Jet OLE DB Provider.

Use Ken Schaefer's troubleshooting site

Unless you're an experienced ASP/SQL programmer, you will encounter these common errors sooner or later. Ken Schaefer has set up an ASP-Access FAQ on his site.

Always use the Jet OLE DB Provider

In Microsoft's words, "When running Microsoft Jet in an IIS environment, it is recommended that you use the native Jet OLE DB Provider in place of the Microsoft Access ODBC driver... The native Jet OLE DB Provider includes fixes and enhancements for stability, performance, and thread pooling" (Source: Microsoft KB article Q222135). If you're seeing errors like this:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open
registry key 'Temporary (volatile) Jet DSN for process 0x5ac 
Thread 0x5a8DBC 0x8ce81c4 Jet'. 

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Unspecified Error 

...then there's a good chance that using the OLE DB provider instead will fix them. "But I see 'OLE DB Provider' in the error message! Doesn't that mean I'm already using it?" No, you're using the OLE DB provider for ODBC drivers, not the provider for Jet. Ken's site has a nice diagram showing the difference, and some instructions on how to make the switch.

If you're already using the Jet OLE DB Provider and are still getting the "Unspecified Error" message, then make sure the permissions are adequate as well.

Limit database writes

In my experience, writing to the database will limit your maximum number of concurrent users more than just reading, i.e. INSERT/UPDATE is less scalable than SELECT SQL statements. Here's an error I got in one ASP application, even using the Jet OLE DB provider and full control permissions:

Microsoft JET Database Engine error '80004005'
Couldn't update; currently locked by user 'Admin' on machine 'PETERPC'.

This was baffling. It seemed that for some reason, if Jet couldn't lock what it needed, it just errors out instead of waiting. And this behavior only showed itself when I subjected it to load testing, with as few as 3 concurrent users.

My solution was to write a function that encapsulates the database update (see below). I try to execute the update, and if it fails, try again, up to 10 times. Testing showed that it never went more than 3 tries before updating successfully. My test had a small number of users (this particular app wouldn't have very many concurrent users), but it was inserting a large amount of data.

Update: Alert reader John Salama informed me that this is what Microsoft recommends doing. "This error occurs when you try to [open a recordset] on an object that can't be locked. There is usually another user who has the same table or tables locked in a way that prevents your action. To respond to the error, wait a short period of time and try the operation again" (Source: Building Applications with Microsoft Access 97, chapter 10, part 4, under "Checking for Errors with Recordset Locking").

Function ExecuteString(strSQLText, objConn)
Dim intTries, blnSuccessful
blnSuccessful = True
Const MAX_TRIES = 10
On Error Resume Next
' We want to try again IF we got an error AND we haven't
' tried the maximum number of times yet
Do
    Err.Clear
    objConn.Execute strSQLText, adCmdText+adExecuteNoRecords
    intTries = intTries + 1
Loop While (Err.Number <> 0) And (intTries < MAX_TRIES)
If Err.Number <> 0 Then
    ' Exceeded the maximum number of tries and was still unsuccessful;
    ' display an error message
    blnSuccessful = False
    Response.Write "An error has occurred!<BR>"
End If
ExecuteString = blnSuccessful
End Function

So if you're doing database writes, the normal rules of scalable database access apply even more. LearnASP has a lesson that explains some of them. Also use GetRows, open your connections/recordsets as late as possible and close them as soon as possible (minimizing the amount of time you've got an open connection to the database), and be sure to set everything to Nothing when you're done with those objects.

Use the latest Jet engine

Microsoft offers a general recommendation that you upgrade everything to the latest and greatest (Source: Microsoft's Web Workshop article, 25+ ASP Tips to Improve Performance and Style, Tip 25). However, there is a philosophy debate inherent in this advice. Upgrading to the latest (in this case, Jet 4.0 SP5 at the time I wrote this article) gets you any bug fixes and feature enhancements. But some people feel that if it ain't broke, don't fix it. My advice is to upgrade to the latest version of Jet; upgrading it has never caused me problems.

As of version 2.6, MDAC (Microsoft Data Access Components) no longer include the Jet database engine components. Even before then, there were Jet updates separate from MDAC updates, which made it confusing to know if you had the latest version, so its exclusion from the MDAC package is probably a good thing.

Here are links to the latest Jet 4.0 (Access 2000 version) service pack, and the latest Jet 3.5 (Access 97 version) service pack, which upgrade those respective versions of the Jet OLE DB Provider.

Q239114 - ACC2000: Updated Version of Microsoft Jet 4.0 Available in Download Center
Q172733 - Updated Version of Microsoft Jet 3.5 Available for Download

A final note on this particular latest-and-greatest debate: I've been using the Jet 4.0 provider with my Access 97 database for months with no problem. I don't know of any reason to use the Jet 3.51 provider instead of the Jet 4.0 provider.