Error message when two different users try to change the same record in a Microsoft Access 2007 database: "Could not update; currently locked"

Consider the following scenario. Two different users access the same Microsoft Office Access 2007 database at the same time. The users try to change the same record. In this scenario, the users receive the following error message: Additionally, after you make changes in a multi-value field on a form and then move to the table without committing the changes, the value is not updated. If you then try to modify the multi-value field in the table, you receive the following error message:

This issue occurs because attachment or multi-value fields use the pessimistic locking feature. Therefore, a lock is held on the field until the record is committed.

You can use the Record Locks property to determine how records are locked and what behavior occurs when two users try to edit the same record at the same time.

When you set the Record Locks property to Edited Record, a page of record is locked as soon as any user starts editing any field in the record. The page of record stays locked until the user moves to another record. Therefore, a record can be edited by only one user at a time. This feature is also named pessimistic locking.

Note Attachment or multi-value fields use pessimistic locking. This behavior occurs even though you set the Default record locking option to No Locks on the Advanced tab of the Options dialog box.

For more information about locking options, see the "RecordLocks Property" topic in the Access 2007 Help file.

To resolve this issue, commit the changes. To do this, use one of the following methods.

Method 1: Save the record

1. Click the Home tab.
2. Under Records, click Save.

Method 2: Refresh the record

1. Click the Home tab.
2. Under Records, click Refresh All, and then click Refresh All or Refresh.

This behavior is by design.

In Access 2007, you can create a field that holds multiple values, such as a list of categories to which you have assigned an issue. These fields can be attachment fields or multi-value lists.

Steps to reproduce the problem

1. Start Access 2007.
2. Create a new Access 2007 database (.accdb file).
3. Right-click the default table, and then click Design View.
4. In the Save As dialog box, type TestTable in the Table Name box, and then click OK.
5. Add a new field that is named Test. To do this, type Test in the Field Name column, and then select Attachment in the Data Type column.
6. Click Save, and then click Datasheet View.
7. Double-click the field that contains the attachment to open the Attachments dialog box.
8. Click Add, locate a file, and then click Open.
9. Click OK.
10. Click the next record to commit the changes to the table.
11. On the Create tab, click Form in the Forms group.
12. In the new form that displays the table data, right-click the attachment, and then click Manage Attachments.
13. In the Attachments dialog box, click Remove, and then click OK.
    
    Note Do not commit the changes.
14. Click the TestTable tab.
15. Double-click the field that contains the attachment. The Attachments dialog box appears.
16. Select the attachment, click Remove, and then click OK.