Access Cookbook

Determine if a Record Is Locked and by Whom

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?

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