ACC: Second OpenRecordset Fails with Run-Time Error 3008

• Microsoft Access versions 7.0, 97

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you use the OpenRecordset method in a Visual Basic for Applications procedure, you may receive the following message:

   Run-time error '3008':
   The table '<tablename>' is already opened exclusively by another user,
   or it is already open through the user interface and cannot be
   manipulated programmatically.

   Run-time error '3008':
   Table '<tablename>' is exclusively locked.

CAUSE

A snapshot-type recordset based on the same underlying table is already open. You may have opened this recordset by opening a form whose Recordset Type property is set to Snapshot, or by using the OpenRecordset method in a Visual Basic for Applications procedure and designating the type with the constant dbOpenSnapshot.

The Microsoft Jet database engine places a shared table-read lock on the underlying table when you open a snapshot-type recordset. This lock persists until you close the recordset in code or close the form. If the recordset is still open and your code uses OpenRecordset with the constant dbDenyWrite to open another recordset based on the same table, the lock on the table is upgraded from a shared table-read lock to a more restrictive exclusive deny-write lock. Even if you then close the second recordset and use the Idle method with the constant dbFreeLocks, the exclusive deny-write lock on the table remains in effect; the Jet database engine upgrades locks to be more restrictive but does not downgrade them to be less restrictive. Therefore, you receive the error message described in the "Symptoms" section when you open another recordset based on that table.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Start Microsoft Access and open the sample database Northwind.mdb.

2. Select the Customers form.

3. On the Edit menu, click Copy.

4. One the Edit menu, click Paste; in the Paste As dialog box, type "frmCustRecords" (without the quotation marks) in the Form Name box.

5. Open frmCustRecords in Design View.

6. On the View menu, click Properties, and then click the Data tab.

7. Change the Recordset Type to Snapshot and close the property sheet.

8. Add a command button to the form and set its Name and Caption properties to "LockTest" (without the quotation marks).

9. Set the OnClick property of the command button to the following event procedure:

   Private Sub LockTest_Click()

             Dim db As Database
             Dim rs As Recordset
             Set db = CurrentDb
             Set rs = db.OpenRecordset("Customers", dbOpenTable, dbDenyWrite)
             rs.Close
             db.Close
             Set db = Nothing
             Set rs = Nothing
             DBEngine.Idle dbFreeLocks
             Set db = CurrentDb
             Set rs = db.OpenRecordset("Customers", dbOpenTable, dbDenyWrite)
             rs.Close
             db.Close
             Set db = Nothing
             Set rs = Nothing
             DBEngine.Idle dbFreeLocks
          End Sub
   
   

10. Close and save the form.

11. Open frmCustRecords and click LockTest. Note that you receive one of the errors described in the "Symptoms" section.

12. Click Debug. Note that the code fails on the line which uses the OpenRecordset method for the second time. The previous instance of the same line of code did not generate the error.

REFERENCES

For more information about the Microsoft Jet database engine locks, refer to the "Microsoft Jet Database Engine Programmer's Guide", pages 193 - 215.

For more information about the constants that you can use with the OpenRecordset method, search the Help Index for "OpenRecordset method."