ACC2000: Other Instances of Access Hang When One User Has a Table Locked

When you try to update data in a table, at some point, Microsoft Access seems not to be responding. The progress bar in Access seems to have stopped advancing, and the pointer becomes an hourglass that persists indefinitely.

A pre-existing transaction has not been committed or has not been rolled back.

Commit or roll back any open transactions before processing new transactions.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

When a database is opened by more than one instance of Access, and a table is locked because of some action, such as editing records in Datasheet view or running an action query against a particular table, other instances of Access may stop responding or may appear to have crashed if there is an attempt to modify records within the same, currently locked, table. This behavior may occur with a shared Access database, such as a database shared on a network.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb in one of two ways:
   • Start two instances of Northwind.mdb on the same computer.
     
     -or-
   • Place a copy of Northwind.mdb on a network share and open the shared database from two different computers.
     
     NOTE: Be sure to open the database in a shared (not exclusive), read/write (not read-only) mode.
2. In one of the instances or workstations, click Queries under Objects, and then click New.
3. Click Design View, and then click OK.
4. In the Show Table dialog box, click Order Details, click Add, and then click Close.
5. Drag the UnitPrice field to the first Field cell.
6. On the Query menu, click Update Query.
7. In the Update To cell of the UnitPrice column, type the following:
   [UnitPrice]*1.1
8. Save the query as qryUpdatePrices, and then close it.
9. Run the qryUpdatePrices query in the first instance or the first workstation. The first of two alert messages appears that reads:
   You are about to run an update query that will modify data in your table.
   
   Are you sure you want to run this type of action query? For information on how to prevent this message from displaying every time you run an action query, click Help.
10. Click Yes. The second of two alert messages appears that reads:
    You are about to update 2155 row(s).
    
    Once you click Yes, you can't use the Undo command to reverse the changes. Are you sure you want to update these records?
11. Before clicking Yes or No on this alert message, run the same query in the second instance or workstation, and click Yes when you receive the first of the two alert messages.
    
    Note that the pointer on the second instance or workstation becomes an hourglass, and the progress bar in the lower-left corner of the Access window stops advancing. The second workstation remains in this state until either Yes or No is clicked on the second of two alert messages in the first instance or workstation. After either Yes or No is clicked, the second instance or workstation proceeds.