Microsoft Jet database engine advice
page 1 of 1
Published: 22 Sep 2003
Unedited - Community Contributed
Abstract
Many ASP applications use an Access database, but few do it right the first time. Here is some tried-and-true advice on using it efficiently in a multiuser environment, and will be continuously updated.
by Peter Johnson
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 77046/ 71

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.



User Comments

Title: Mr   
Name: vbchr27
Date: 4/2/2009 3:56:22 PM
Comment:
yes throw away a jewell to trash. The Jet enngine is still in access 2007. why ? because it deserve it.
You can do a lot of intranet (win and web) apps with jet and access/.net, beating easly the vb.net at RAD developing.
Oldies but goldies.
Long live the king!!!!!
Title: This could help   
Name: Rita Roy
Date: 7/28/2008 2:35:38 PM
Comment:
I noticed a similar error a few months back in my application. After a week of head ache I could find a solution. Thanks to Kanbal.com. Try the solution in the below mentioned url. This could be bcos of the IUSR account permissions or the usage of MS access keywords in column names. Check it out at:-

http://www.kanbal.com/index.php?/Int...ied-error.html

Hope this helps.
Title: Microsoft JET Database Engine(0*80004005)   
Name: megan
Date: 6/17/2008 12:43:10 AM
Comment:
Hi..
mow only i am entering into ASP web development.
when i tried it in my home, i am getting the above (title)mentioned error.

as well as when i try to update the database it gives "Operation must use an updateble query".

what should i do?
Title: Microsoft JET Database Engine error '80004005' Unspecified error   
Name: Mohammad Al-alameh
Date: 3/21/2007 11:07:39 AM
Comment:
This error happened when no of concurrent DB copnnections become very big ….

And the IUSR_machinename is limited,,, it will cuz the IUSR to become full



Solution :



Increase quota limit for IUSR_machinename from Quota entris
Title: unspecified error   
Name: Rafael Soares
Date: 1/26/2007 4:57:32 PM
Comment:
I'm getting this error in the first time a load the database.

Microsoft JET Database Engine error '80004005'

Unspecified error

Modify the system settings is not an option since the database is in a remote host. The host is running on Windows 2003 Standard, the directory that contains the db has Read/Write permissions.
The database was working fine, suddenly the error appeared...
If someone knows what could I do, plase e-mail me: rcruentus_fck@yahoo.com.br
thanks
Title: Use the latest Jet engine   
Name: kamalkanth
Date: 5/13/2006 9:38:00 AM
Comment:
Hi Realy it is useful one
Title: RE: Microsoft.Jet.OLEDB.4.0   
Name: Peter
Date: 3/20/2006 2:45:00 PM
Comment:
Broun,

Are you using the latest Jet engine as advised above? It sounds like your new computer may be using an older engine.
Title: Microsoft.Jet.OLEDB.4.0   
Name: Broun
Date: 3/20/2006 1:49:18 PM
Comment:
Am using this script to access the MSAccess database to update login and other details for my website. I however changed the computer to another(it used to work on MSAcess 2000 but on MSAccess 2003 it doesnt work). The work am doing is just testing as a student am not developing a big project.
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & src

Please advise me on the provider i should use to access my MSAcess database. It will take me alot of time to change to another program.
Title: RE: unspecified error   
Name: Peter
Date: 7/22/2005 10:20:00 AM
Comment:
Assuming you're using an Access database, you should always use the OLE DB provider, not the ODBC driver:

http://aspalliance.com/14#oledb

Also make sure you always close your connections and recordsets as soon as you're done with them, and set them to Nothing.
Title: unspecified error   
Name: ashok
Date: 7/22/2005 8:19:03 AM
Comment:
I am using IIS with Asp to generate a report in which i am using multiple search queries based on condition. But when i choose multiple options to run my page at the same time, it is giving me unspecified error, odbc error. If i choose for example out of 250 option let's say 100 i can get the report executed.
Title: Problem persists with Updated version   
Name: vinod
Date: 6/20/2005 7:48:29 AM
Comment:
The same problem,

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

encountered with us. Asper your instructions we installed the latest service patch for Access2000.

Even, after installing the latest patch the problem persists. So can you pls give me the solution.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 10/31/2014 11:59:07 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search