Access Cookbook - Ken Getz [220]
Figure 10-24. A record has been locked, but by whom?
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:
Import the basRecordLock module from 10-09.MDB into your database.
Add a command button to each form with the following in the command button's OnClick property:
=acbWhoHasLockedRecord([Form])
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