Microsoft KB Archive/225926

= ACC2000: Record-Level Locking Does Not Appear to Work =

Article ID: 225926

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q225926



This article applies only to a Microsoft Access database (.mdb).

Moderate: Requires basic macro, coding, and interoperability skills.



SYMPTOMS
In a multiuser environment, two or more users are allowed to edit the same record at the same time, even though record-level locking has been turned on.



STATUS
This behavior is by design.



MORE INFORMATION
By default, Access 2000 uses optimistic locking (that is, Access sets the Default record locking option to No locks). This allows two or more users to edit the same record at the same time. You must change this default to pessimistic locking (that is, set the Default record locking option to Edited record). Optimistic locking only enforces a lock when one user tries to save a change after another user has already changed the same record. Optimistic locking overrides record-level locking, even if the Open databases using record-level locking checkbox has been selected. Record-level locking is invoked when pessimistic (that is, the Default record locking option is set to Edited record) is selected.

When you use page-level locking (rather than record-level locking), multiple records can become locked while a user is editing a single record. In other words, all records that fit within the 4 kilobyte (4096 byte) page will be locked, even though only one of the records within that page is being edited.

With record-level locking, however, if a user edits any one record in a table, other users are able to edit any other record, except that one. But this is only true when both pessimistic and record-level locking has been set within the Microsoft Access Options dialog box.

The following steps show how record-level locking appears to not be working.

Steps to Reproduce Behavior
 One the same computer, start Microsoft Access 2000 twice, and open the sample database Northwind.mdb in each instance of Access. In the first instance of Access 2000, on to the Tools menu, click Options, and then click the Advanced tab. Make sure the Open databases using record-level locking check box is selected, make sure Default record locking is set to No locks (optimistic locking), and then click OK. Repeat steps 2 and 3 for the second instance of Access.

NOTE: If you had to click to select the Open databases using record-level locking check box in either instance, close that instance, and then reopen the sample database Northwind.mdb. Open the Categories table in both instances. In the first instance, for CategoryID 4, change the Description box from Cheeses to Cheeses and Eggs. Do not save the change.</li> Edit the same record in the second instance, but change the value to Cheeses and Milk. Note that although record-level locking is invoked, you are allowed to edit the value in both instances. If you now save the change in the second instance, the first instance will generate the following Write Conflict error if you try to save its change:

This record has been changed by another user since you started

editing it. If you save the record, you will overwrite the changes

the other user made.

Copying the changes to the clipboard will let you look at the values

the other user entered, and then paste your changes back in if you

decide to make changes.

</li></ol>

<div class="references_section">