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.