Records locks don't release - error 3260/3187
I've written a call tracking application for the tech center of a major
automotive after-market company.
Everybody is running the same application from a networked server. The
workstatons run NT. The network apears to be Netware. I have never seen
the server that the program is being stored on - and they have a lot of
servers at this place.
The techs enter a small amount of information concerning the just finished
tech call into an unbound form. When they are done they click a button to
"log a call". This event uses VB code to write this information to a new
data record in the table - checking for lock errors. This is the only time
the table is open. It is closed again after the update.
Some times they get a 3260 (Couldn't update; currently locked by user '|2'
on machine '|1') or 3187 (Couldn't read; currently locked by user '|2' on
machine '|1') errors which either don't go away or take a very long time to
clear out.
When we go to the machine that the message says is holding the lock, the
form is sitting waiting for data entry. It is not in the adding record
routine and so (according to the code) doesn't even have the table in
question open.
If we exit the program on the machine that the message says has the lock,
the other users can immediately write their records.
Does anybody know why the lock is persisting after the recordset has been
closed and the subprocedure exited?
I've just read about the "dbEngine.Idle (Refresh Cache)" method and wondered
if that should be included in my code after I close the recordset?
Would changing the application into a front end / back end application clear
up this type of lock persistents (they liked having everybody use the same
copy of the database for ease of maintainenance.)
Any thoughts would be appreciated.
A summary of the codes is included below. Thank you.
----------------------------------
Private Sub LogCall_Click()
'***
'*** Write the record to the Table TechCall
'*** Minimize the form if the user default (vMinimizeOnLog) is set to
minimze.
'***
Dim rstTechCall As Recordset
Dim fld As Field, iLockCount As Integer, strmsg As String
On Error GoTo LogCall_Error
'Make sure all required fields have been populated
If required fields not populated
Appropriate message and action
'Otherwise write the record
Else
'open table
Set rstTechCall = dbs.OpenRecordset("TechCall")
'and set locking to optimistic - page will be available until
update
'(pessimistic always occurs if during addnew the record will
span a page)
rstTechCall.LockEdits = False
'turn on the hourglass and
DoCmd.Hourglass True
'start adding new record
On Error GoTo Lock_Error
rstTechCall.AddNew
On Error GoTo LogCall_Error
For Each fld In rstTechCall.Fields
'the Form's control names are the same as the Table
TechCall's field names
fld.Value = Me(fld.name).Value
Next fld
'finish adding new record,
On Error GoTo Lock_Error
rstTechCall.Update
On Error GoTo LogCall_Error
'and close the connection to the table
rstTechCall.Close
' Call the Idle method to release unneeded locks, force
' pending writes, and refresh the memory with the current
' data in the .mdb file.
DBEngine.Idle dbRefreshCache
'turn off the hourglass
DoCmd.Hourglass False
're-initialize the form (zero it out)
Call ResetForm
'Minimizes the form?
If vMinimizeOnLog Then
DoCmd.RunCommand acCmdAppMinimize
End If
End If
Exit_LogCall:
Exit Sub
LogCall_Error:
MsgBox Err & ": " & Err.Description
DoCmd.Quit
Lock_Error:
'See if Error is a 'Table Lock' or 'Open Exclusive' error
If InStr(Err.Description, "lock") Or InStr(Err.Description, "exclusiv")
Then
'increment how many times we've gotten here
iLockCount = iLockCount + 1
'if it's been more than 5 times ask if they want to continue
waiting
If iLockCount > 5 Then
DoCmd.Hourglass False
strmsg = appropriate message
'yes - continue waiting for the recordset to unlock
If MsgBox(strmsg, vbYesNo + vbQuestion, "Retry Logging
Call?") = vbYes Then
DoCmd.Hourglass True
iLockCount = 1
'no - close the connection to the table and return to the
unaltered form
Else
rstTechCall.Close
Resume Exit_LogCall
End If
End If
'pause for 20 - 200 milliseconds
sSleep (20 + Int(Rnd * 180 + 0.5))
'and try again
Resume
'all other error
Else
MsgBox Err & ": " & Err.Description
DoCmd.Quit
End If
End Sub
