ACC: Cannot Edit Record from Two Forms in One Session

• Microsoft Access versions 1.0, 1.1, 2.0

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

You cannot edit the same record from two different forms or datasheets in a single session, or instance, of Microsoft Access even though you have set the Microsoft Access locking scheme to No Locks or optimistic record locking.

CAUSE

The record locking scheme in Microsoft Access defaults to Edited Record, or pessimistic record locking, on a per-record basis within a single instance of Microsoft Access.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access versions 1.0, 1.1, and 2.0. This problem no longer occurs in Microsoft Access version 7.0.

MORE INFORMATION

Microsoft Access has three record locking schemes. The No Locks, or optimistic, record locking scheme permits multiple users to edit the same record simultaneously. When one user saves a record, other users editing the same record receive notification from Microsoft Access, and are allowed to overwrite the record, abandon it, or copy changes to the Clipboard.

The Edited Record, or pessimistic, record locking scheme permits only one user to edit a record at a time. When a record is being edited, other users who attempt to edit the record are locked out.

The All Records locking scheme instructs Microsoft Access to lock all the records in a form or datasheet (and underlying tables) for the entire time you have the form or datasheet open. This is an extremely restrictive record locking scheme.

To set the record locking scheme, choose Options from the View menu, select the Multiuser/ODBC category, and then select the scheme you want for the Default Record Locking item. You can also set the record locking scheme by setting the RecordLocks property for a form. In Microsoft Access version 2.0, you can also set the RecordLocks property for queries and reports. The Default Record Locking setting applies to table datasheets and other objects that do not have an explicit RecordLocks property.

No matter which record locking scheme you set, however, Microsoft Access ignores the setting and uses pessimistic record locking within a single instance of Microsoft Access.

Steps to Reproduce Problem

1. Open the sample database NWIND.MDB.

2. From the View menu, choose Options. In the Category box, select Multiuser/ODBC.

3. Set the Default Record Locking item to No Locks, and then choose OK.

4. Open the Categories table in Datasheet view. Modify the first record in the table, but do not save the changed record.

5. Open the Categories form in Form view and try to modify the first record in the form. Note that you cannot edit the record.

REFERENCES

Microsoft Access "User's Guide," version 1.1, Chapter 16, "Using Microsoft Access in a Multiuser Environment," pages 388-389

Microsoft Access "User's Guide," version 2.0, Chapter 6, "Sharing Data in a Multiuser Environment," pages 106-108