Access Reports Records Locked By Users When There Are No Locks

[drosenkranz]

I'm using VB 5.0 (Service Pack 3) and Access 97 DAO. The workstations are running Win 95. I have as many as thirty five users on the system at peak- most are browsers. Users occasionally come up with my error message which will read "Record is currently locked by user JSmith" when attempting to save their changes.

The problem is that the offending user, JSmith in this example, did not actually have a lock on their records. In fact, sometimes the offending user is no longer even on the system any longer when Access reports back their name as still locking a record. Sometimes, the users are in separate (unrelated) tables when this occurs too.

Actually, records are only locked for an instant when the user presses the <Update> button. It almost appears that the Access table is &quot;lagging behind&quot; the real time events- but I mean really behind. What causes this behavior? Access? Network traffic?

Is there a command or setting that I could include (in a button) on the form that will allow the user who is locked out to isuue a &quot;catch-up&quot; command to the database?

Any ideas out there?

[sig][/sig]

 

[richrider]

drosenkranz

I have been working on the same problem myself with no success at the moment. My problems comes because I don't know how to successfully trap errors yet (teaching myself vb - no official training).

Here is the breakdown on the error (assuming it is error 3260: Couldn't update; currently locked by user '<name>' on machine '<number>'). According to Microsoft, page locking is done on a per page basis. A page is considered what ever can fit into a 2K block of memory. - only happens when one user decides to save or update the information they are working on, which may be partially contained in a different 2K memory block. Resulting in the error.

Possible solutions that have been floating around are the following - [note I haven't tried all of them as of yet]

1. Create an MDE file in Access and direct the VB code to the MDE file - however there are limitations that are presented in Access about doing this (read under MDE files in Access).

2. MSDN does a good job with providing sample code which tackles the areas of page locking, database locking, and record locking. I will find the link and supply it later.

3. The one I am trying to work on - if you can provide any help as well, it might work. Some have said to try to trap the error 3260 and essentially delay the saving or updating for a couple of seconds until the lock on the record is off (whether it be by trying to send the command 3 times before quitting or something of that nature). This one seems to be the most feasible from my POV. If you know how to trap the error can you share the code you used to just trap it and I might be able to help you from there.

I hope I can help you further because I know how frustrating this problem is. I hope this can help pointing in the right direction.

Richrider
[sig][/sig]

 

[drosenkranz]

The behavior I'm talking about is not because the record is &quot;page locked&quot; in another user's application, its because Access just can't seem to keep up with the database. I have users who Log off and my .mdb &quot;snoop&quot; utility shows that they;re still on the system 10 minutes after they've logged off.

In response to your code request, this is one of the routines that I use. You should always preceed a file access command (like .update or .refresh) with an On Error statement which calls a routine to deal with it. In the actual program code section, you might have the following Code Segment #1:

On Error Go To DataError
datAccessRS.Recordset.Update


At the bottom of this program code section (before End Sub), you'd put Code Segment #2:

DataError:
RecLockError
Blah...
Blah...
Blah...
Exit Sub' or what ever action(s) you want this program module to do after that error


I Create a module named RecLockErrorSub in which I include the following Code Segment #3 :


Public Sub RecLockError()
'Error messages for Record Locking

Dim strErrMsg As String

Select Case Err.Number

Case 524
strErrMsg = &quot;An 'Item Association' Error Occured Causing The Update To Fail&quot; & vbCrLf & vbCrLf
strErrMsg = strErrMsg & &quot;This Occurrence Was 'Probably' A Temporary Error Within The JET Engine.&quot; & vbCrLf & vbCrLf
strErrMsg = strErrMsg & &quot;You Should Retry The Operation Now Or Try It Again Later.&quot; & vbCrLf & vbCrLf
strErrMsg = &quot;Error: &quot; & Err.Number & &quot; &quot; & Err.Description

Case 3260
strErrMsg = &quot;Unable To Lock Record For Editing Now: (Already In Use) - Please Try Again Later.&quot; & vbCrLf & vbCrLf
strErrMsg = strErrMsg & &quot;Error: &quot; & Err.Number & &quot; &quot; & Err.Description

Case 3197
strErrMsg = &quot;Sorry! Record Has Changed (By Another User) Since You Opened It.&quot; & vbCrLf & vbCrLf
strErrMsg = strErrMsg & &quot;Error: &quot; & Err.Number & &quot; &quot; & Err.Description

Case 3167
strErrMsg = &quot;Sorry! Record Was Deleted (By Another User) Since You Opened It.&quot; & vbCrLf & vbCrLf
strErrMsg = strErrMsg & &quot;The Record No Longer Exists In The Original Data Table.&quot; & vbCrLf & vbCrLf
strErrMsg = strErrMsg & &quot;Error: &quot; & Err.Number & &quot; &quot; & Err.Description

Case Else
strErrMsg = vbCrLf
strErrMsg = strErrMsg & &quot;Error: &quot; & Err.Number & &quot; &quot; & Err.Description

End Select
MsgBox strErrMsg, vbOKOnly, &quot;Error: Record Locking Failure&quot;
mstrRecLockError = &quot;Y&quot;
Exit Sub
End Sub

Code Segment #3 allows all of my program modules to share the RecLockError() while the actions taken in each program module are handled

Code Segment #2's (Blah Blah Blah) allows each program module to execute additional instructions after the error has occurred. You could even change Code Segment #2's &quot; Exit Sub&quot; to a &quot;Resume Next&quot; if you resolved the error in this code and allow the program to continue on with what would have been the next sequential instruction immediately following the line that caused the original error.

This is just one way of doing it. Best of luck. [sig][/sig]