Recipe 10.9 Determine if a Record Is Locked and by Whom

10.9.1 Problem

When you use pessimistic locking (discussed in the upcoming sidebar) in your applications, Access informs you if another user has locked a record by displaying an icon in the record selector of the form's detail section (shown in the upper-left corner of Figure 10-24). While this is nice, you may want to know who actually has the record locked. Is there any way to determine this?

Figure 10-24. A record has been locked, but by whom?

10.9.2 Solution

There is no built-in menu command or toolbar button that tells you who has a record locked, but you can create a VBA function that returns the username and the machine name of the user who has the current record locked. This solution shows you how to create such a function that you can call from any form.

Start Access and load the same copy of the 10-09.MDB database on at least two machines on your network. (Alternately, you can use two instances of Access on a single machine.)

Customizing the Record-Locking Method In Access 2000, Microsoft added an important new capability to the Jet database engine: record-level locking. In previous versions, if you locked the record being edited, you would also lock any other records that happened to be on the same data page as the edited one. A data page held 2,048 characters, so it was likely that locking would affect more than one record. In Access 2000, Microsoft increased the size of data pages from 2,048 characters to 4,096 characters in order to support Unicode characters, which each consume 2 bytes. With such large pages, Microsoft decided that it needed to allow you to lock single records. In the Advanced page of the Tools Options dialog, you can now choose to open the database using record-level locking. This avoids locking entire pages when locking the edited record. That dialog also allows you to select a default method of locking, which is applied to data sheets and to any new forms. To change the method of locking for a form, open the form in design mode and modify the value of the form's RecordLocks property. If this property is set to EditedRecord, Access uses pessimistic locking for the form, which means that Access locks the page of records or the single record as soon as you change any data on the form (when the pencil icon appears in the form's record selector). If it's set to NoLocks, Access uses optimistic locking for this form, which means that Access locks the page of records or the single record only at the moment you save your changes. For most forms, optimistic locking is the preferable setting, because it keeps records locked for a much shorter period of time. Sometimes, however, you'll need to employ pessimistic locking to ensure that no more than one user is editing a record at the same time. Record-level locking makes pessimistic locking much more practical, as it ensures that only the record being edited will be locked.

Open the frmEmployees form on the first machine (or instance), changing the data in any control of the form so that the pencil icon appears in the form's record selector. Don't release the lock by saving the record, and open the same form on the second machine. On the second machine, press the button with the image of a padlock. A message box should appear displaying the username and machine name of the user on the first machine who has locked the record (see Figure 10-25). (To get an accurate username, both machines should share the same system database file with security enabled. For more information on enabling security, see the Solution in Recipe 10.1.)

Figure 10-25. The username and machine name of the user who has locked the current record

To add a lock identification button to your own forms, follow these steps:

10.9.3 Discussion

The acbWhoHasLockedRecord function's code is shown here:

Public Function acbWhoHasLockedRecord(frm As Form)
    ' Display a message box that says either:
    '  -No user has the current record locked, or
    '  -The user & machine name of the user who
    '   who has locked the current record.
    
    Dim rst As DAO.Recordset
    Dim blnMUError As Boolean
    Dim strUser As String
    Dim strMachine As String
    Dim strMsg As String
    
    On Error GoTo HandleErr
    
    ' Default message
    strMsg = "Record is not locked by another user."
    
    ' Clone the form's recordset and synch up to the
    ' form's current record
    Set rst = frm.RecordsetClone
    rst.Bookmark = frm.Bookmark
    
    ' If the current record is locked, then the next
    ' statement should produce an error that we will trap
    rst.Edit
    
ExitHere:
    ' Display either the default message or one specifying
    ' the user and machine who has locked the current record.
    MsgBox strMsg, , "Locking Status"
    Exit Function
    
HandleErr:
    ' Pass the error to acbGetUserAndMachine which will attempt
    ' to parse out the user and machine from the error message
    If Err.Number = 3188 Then
        ' Locked on this machine.
        strMsg = "Some other part of this application " _
         & "on this machine has locked this record."
    Else
        blnMUError = acbGetUserAndMachine(Err.Description, _
         strUser, strMachine)
        ' If the return value is True, then acbGetUserAndMachine
        ' was able to return the user and machine name of the user.
        ' Otherwise, assume the record was not locked.
        If blnMUError Then
            strMsg = "Record is locked by user: " & strUser & _
             vbCrLf & "on machine: " & strMachine & "."
        End If
    End If
    Resume ExitHere
End Function

This function accepts a single parameter: a pointer to a form. Using this form object, acbWhoHasLockedRecord clones the form's recordset, synchronizes the clone's current record with that of the form, and attempts to lock the current record. One of two things can happen as a result of this locking attempt:

• The attempt will succeed, meaning that the record was not locked by another user.

• The attempt will fail with an error message stating who has the record locked.

By parsing this error message, we can determine who has locked the record. Parsing the error message is accomplished by the acbGetUserAndMachine function, which is shown here:

Public Function acbGetUserAndMachine(ByVal strErrorMsg As String, _
 ByRef strUser As String, ByRef strMachine As String) As Boolean
    ' Parse out the passed error message, returning
    '  -True and the user and machine name
    '   if the record is locked, or
    '  -False if the record is not locked.
    
    Dim intUserPos As Integer
    Dim intMachinePos As Integer
    
    Const USER_STRING As String = " locked by user "
    Const MACHINE_STRING As String = " on machine "
  
    acbGetUserAndMachine = False
    
    On Error Resume Next
    intUserPos = InStr(strErrorMsg, USER_STRING)
    If intUserPos > 0 Then
        intMachinePos = InStr(strErrorMsg, MACHINE_STRING)
        If intMachinePos > 0 Then
            strUser = Mid$(strErrorMsg, _
             intUserPos + Len(USER_STRING), _
             intMachinePos - (intUserPos + Len(USER_STRING) - 1))
            strMachine = Mid$(strErrorMsg, _
             intMachinePos + Len(MACHINE_STRING), _
             (Len(strErrorMsg) - intMachinePos - _
             Len(MACHINE_STRING)))
        End If
        acbGetUserAndMachine = True
    End If
End Function

This function accepts as its argument the Description property of the Err object, which was generated by acbWhoHasLockedRecord. If it can successfully parse the error message and determine at least the username (and hopefully the machine name), it returns a True value to the calling routine with the names of the user and machine as the second and third parameters of the function call. There's nothing magic about this function—it uses the InStr function to locate certain landmarks in the passed error message.

Record-level locking makes the use of pessimistic locking much more practical than it has been in the past. However, you still run the danger of allowing a user to monopolize the record being edited. This solution shows how you can identify the guilty user, but it doesn't really solve the problem. The next solution enables you to prevent users from tying up records for longer than a set period of time.