K
kiln
I've read around here and there about record locking and have come up
with kind of an incomplete mixed bag of results. A particular app I'm
working on is more prone to concurrent record editing contention than
most I've done. Here are some bits of info an experience that I'd be
glad to hear comments on. This is jet all the way, as secured as Access
can be, Access 2000 soon-to-be Access 2003, with the regular FE BE split
format, each workstation running it's own copy of the db FE.
I've read that recordset .EditMode is kind of useless and have not been
able to get it to return a value other than 0...but that was early on in
my testing and maybe I missed something.
Probably optimistic locking is not the best for this app. If one user
has started to edit a record, I want any other user that starts to edit
the rec to be warned and prevented from continuing.
I'm intersted in hearing aobut whatever works for any reader, but what
seems to be regarded as the best route for preventing concurrent edits
is to set the form to Record Locks = Edited Record, aka pessimistic
locking, and then use a recordset to try to edit that rec; if .edit
produces an error, bail out of the second users edit attempt (probably
in the On Dirty event of the form?) But what I don't understand is that
even with tiny data populations and FE aned BE on the same pc, it takes
at least a second for the routine to cough up the msg that the rec can't
be edited and then back out the changes. How can it take so long? I'll
test more but in the meantime here is the test code (IsRecordsetLocked()
was more or less blindly copied from a post here):
Private Sub Form_Dirty(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim boolLocked As Boolean
Dim strMsg As String
Set db = CurrentDb
strSQL = "Select qryPerson.* FROM qryPerson WHERE qryPerson.PID = " &
PID
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
boolLocked = IsRecordsetLocked(rs, strMsg)
MsgBox "IsRecordsetLocked " & boolLocked
If boolLocked = True Then
Cancel = True
End If
End Sub
Function IsRecordsetLocked(rst As Recordset, strErrMsg As String) As
Boolean
' Checks the current record in 'rst' and retuns True if it (or the
' page its in) is locked by another user or another process on
' the same machine, otherwise False.
On Error GoTo err_IsRecordsetLocked
Const cnsLockPageSave = 3186
Const cnsLockPageRead = 3187
Const cnsLockLocalUpd = 3188
Const cnsLockExclusive = 3189
Const cnsLockRecSave = 3202
Const cnsLockRecUpd = 3218
Const cnsLockPageUpd = 3260
IsRecordsetLocked = False
strErrMsg = ""
' Try to edit the current record in the recordset.
rst.Edit
' If we get here, No locks apply so return False.
rst.CancelUpdate
exit_IsRecordsetLocked:
Exit Function
err_IsRecordsetLocked:
Select Case Err.Number
Case cnsLockPageSave, cnsLockPageRead, cnsLockLocalUpd, _
cnsLockExclusive, cnsLockRecSave, cnsLockRecUpd, _
cnsLockPageUpd
IsRecordsetLocked = True
strErrMsg = Err.Description
Case Else
MsgBox "An Error has occurred (" & Err.Number & ")" & _
vbCrLf & Err.Description
End Select
Resume exit_IsRecordsetLocked
End Function
with kind of an incomplete mixed bag of results. A particular app I'm
working on is more prone to concurrent record editing contention than
most I've done. Here are some bits of info an experience that I'd be
glad to hear comments on. This is jet all the way, as secured as Access
can be, Access 2000 soon-to-be Access 2003, with the regular FE BE split
format, each workstation running it's own copy of the db FE.
I've read that recordset .EditMode is kind of useless and have not been
able to get it to return a value other than 0...but that was early on in
my testing and maybe I missed something.
Probably optimistic locking is not the best for this app. If one user
has started to edit a record, I want any other user that starts to edit
the rec to be warned and prevented from continuing.
I'm intersted in hearing aobut whatever works for any reader, but what
seems to be regarded as the best route for preventing concurrent edits
is to set the form to Record Locks = Edited Record, aka pessimistic
locking, and then use a recordset to try to edit that rec; if .edit
produces an error, bail out of the second users edit attempt (probably
in the On Dirty event of the form?) But what I don't understand is that
even with tiny data populations and FE aned BE on the same pc, it takes
at least a second for the routine to cough up the msg that the rec can't
be edited and then back out the changes. How can it take so long? I'll
test more but in the meantime here is the test code (IsRecordsetLocked()
was more or less blindly copied from a post here):
Private Sub Form_Dirty(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim boolLocked As Boolean
Dim strMsg As String
Set db = CurrentDb
strSQL = "Select qryPerson.* FROM qryPerson WHERE qryPerson.PID = " &
PID
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
boolLocked = IsRecordsetLocked(rs, strMsg)
MsgBox "IsRecordsetLocked " & boolLocked
If boolLocked = True Then
Cancel = True
End If
End Sub
Function IsRecordsetLocked(rst As Recordset, strErrMsg As String) As
Boolean
' Checks the current record in 'rst' and retuns True if it (or the
' page its in) is locked by another user or another process on
' the same machine, otherwise False.
On Error GoTo err_IsRecordsetLocked
Const cnsLockPageSave = 3186
Const cnsLockPageRead = 3187
Const cnsLockLocalUpd = 3188
Const cnsLockExclusive = 3189
Const cnsLockRecSave = 3202
Const cnsLockRecUpd = 3218
Const cnsLockPageUpd = 3260
IsRecordsetLocked = False
strErrMsg = ""
' Try to edit the current record in the recordset.
rst.Edit
' If we get here, No locks apply so return False.
rst.CancelUpdate
exit_IsRecordsetLocked:
Exit Function
err_IsRecordsetLocked:
Select Case Err.Number
Case cnsLockPageSave, cnsLockPageRead, cnsLockLocalUpd, _
cnsLockExclusive, cnsLockRecSave, cnsLockRecUpd, _
cnsLockPageUpd
IsRecordsetLocked = True
strErrMsg = Err.Description
Case Else
MsgBox "An Error has occurred (" & Err.Number & ")" & _
vbCrLf & Err.Description
End Select
Resume exit_IsRecordsetLocked
End Function