ACC2000: Second OpenRecordset Fails with Run-Time Error 3008 (197952)

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 error 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.

This error occurs the second time that you use the OpenRecordset method in your procedure. The first line in which you use the OpenRecordset method to open a recordset based on the same table does not generate an error.

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 dbOpenSnapshot constant.

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.

MORE INFORMATION

Steps to Reproduce Behavior

Open the sample database Northwind.mdb.
Select the Customers form.
On the Edit menu, click Copy.
One the Edit menu, click Paste; in the Paste As dialog box, type frmCustRecords in the Form Name box.
Open the frmCustRecords form in Design View.
On the View menu, click Properties, and then click the Data tab.
Change the Recordset Type to Snapshot and close the property sheet.
Add a command button to the form and set its Name and Caption properties to LockTest.

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

Close and save the form.
Open the frmCustRecords form and click LockTest. Note that you receive one of the errors described in the "Symptoms" section.
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.