Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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.)

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:

1. Import the basRecordLock module from

   10-09.MDB
   into your database.

2. Add a command button to each
   form with the following in the command button's
   OnClick property:

   =acbWhoHasLockedRecord([Form])

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 functionit
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.