Microsoft KB Archive/172617
Article ID: 172617
Article Last Modified on 1/20/2007
- 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.
When you use the OpenRecordset method in a Visual Basic for Applications procedure, you may receive the following message:
If you are using Microsoft Access version 7.0, you may receive the following message:
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.
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.
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.
- 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 frmCustRecords 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.
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."
Additional query words: MS
Keywords: kbprb KB172617