Microsoft KB Archive/172617

= ACC: Second OpenRecordset Fails with Run-Time Error 3008 =

Article ID: 172617

Article Last Modified on 1/20/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q172617



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



SYMPTOMS
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 ' ' is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically.

If you are using Microsoft Access version 7.0, you may receive the following message:

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

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.



STATUS
This behavior is by design.



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 frmCustRecords in Design View.</li> On the View menu, click Properties, and then click the Data tab.</li> Change the Recordset Type to Snapshot and close the property sheet.</li> Add a command button to the form and set its Name and Caption properties to LockTest .</li>  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 </li> Close and save the form.</li> Open frmCustRecords and click LockTest. Note that you receive one of the errors described in the "Symptoms" section.</li> 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.</li></ol>

<div class="references_section">