|[ Team LiB ]|
Recipe 10.9 Determine if a Record Is Locked and by Whom
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?
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.)
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.)
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:
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.
|[ Team LiB ]|