ACC2000: Second OpenRecordset Fails with Run-Time Error 3008

• Microsoft Access 2000

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.

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.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. 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 in the Form Name box.



5. Open the frmCustRecords form 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.



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 the frmCustRecords form 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 constants that you can use with the OpenRecordset method, click Microsoft Access Help on the Help menu, type OpenRecordset method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: MS

Keywords : kbdta MdlRcd MdlDao
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb