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