Microsoft Jet Error

Recently we upgraded our Microsoft Visual Studio from 2003 to 2005. Prior to this, we did not encounter problems whenever we give online examinations in our computer laboratory. We used single user login for all students taking the online exam. There are about 90 students taking the exam at the same time using one username. But after the upgrade, we encountered this Microsoft Jetway error. Right now we are still trying to find the cause of this error. The immediate remedy that we made is creating another user account for other users. After adding a new user account, the error is gone. We know that this is not the solution for this problem but for the mean time, it works.

Update:

A solution for the problem above can be found here  http://aspalliance.com/14_Microsoft_Jet_database_engine_advice and I quote …

“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.

“