PRB: Requery Method Changes LockType Property of ADO Recordset to Read-Only

Under certain circumstances, if you call the Requery method of an ActiveX Data Objects (ADO) Recordset object, the LockType property of the Recordset changes to read-only (adLockReadOnly).

This problem may occur under the following circumstances:

• If you use DataEnvironment command objects to build hierarchical (shaped) recordsets. -or-
  
  
• If you call Requery after you disconnect and reconnect the Recordset in both hierarchical and standard disconnected ADO recordsets.

To resolve this problem, use code that is similar to the following code (instead of calling Requery) to close and to reopen the Recordset:

Because this behavior is specific to the DataEnvironment object, the same result occurs with any OLE DB provider and with any CursorLocation or CursorType property setting. However, this problem does not occur if you use DataEnvironment to create a non-hierarchical ("flat") Recordset.

Steps to Reproduce Behavior

Hierarchical Recordsets Using DataEnvironment Commands

1. Follow these steps to set up the project and create the connection:
   1. In Visual Basic, create a new Standard EXE project. Form1 is added to the project by default.
   2. On the Project menu, click Add DataEnvironment to add this component to your project.
   3. In the DataEnvironment window, right-click Connection1, and then click Properties.
   4. Change the properties to point to the Microsoft Access Northwind sample database and to test the connection, and then click OK.
2. Follow these steps to create the parent and child commands of the hierarchical Recordset and to set up the DataEnvironment to return a hierarchical Recordset:
   1. In the DataEnvironment window, right-click Connection1, and then click Add Command. This adds Command1 to the DataEnvironment.
   2. Right-click Command1, and then click Properties.
   3. In the Properties dialog box, click the General tab. Under Source of Data, click SQL Statement, and then paste the following code in the text box:

      SELECT CustomerID, EmployeeID, Freight, OrderDate, OrderID, RequiredDate FROM Orders
      						

   4. On the Advanced tab, set the LockType property to 3-Optimistic, and then click OK.
   5. In the DataEnvironment window, right-click Command1, and then click Add Child Command. The Command2 child command is added directly to the list of fields under Command1.
   6. Right-click Command2, and then click Properties.
   7. In the Properties dialog box, click the General tab. Under Source of Data, click SQL Statement, and then paste the following code in the text box:

      SELECT Discount, OrderID, ProductID, Quantity, UnitPrice FROM [Order Details]
      						

   8. Click Apply to accept the changes. You receive the following warning:
      At least one relation must be defined.
      Click OK.
   9. To create this relation, click the Relation tab in the Properties dialog box, and then ensure that Relate to Parent Command Object is selected.
   10. Confirm that the OrderID field appears in both the Parent Fields and the Child Fields of the Relation Definition section, and then click Add.
   11. Click OK, and then close the DataEnvironment window.
3. Follow these steps to set up Form1:
   1. Add a TextBox control to Form1. Set the DataSource property to DataEnvironment1, set the DataMember property to Command1, and then set the DataField property to CustomerID.
   2. Add a Button control to Form1, and then paste the following code in the Click event handler of the button:

           Debug.Print "LockType Before Requery:  " & DataEnvironment1.rsCommand1.LockType
           DataEnvironment1.rsCommand1.Requery
           Debug.Print "LockType After Requery:  " & DataEnvironment1.rsCommand1.LockType
      						

4. Run the project, and then click the command button. The following output appears in the immediate window:

   LockType Before Requery:  3  (Optimistic Locking)
   LockType After Requery:  1   (Read-Only Recordset)
   						

5. To work around this behavior, replace the following code

        DataEnvironment1.rsCommand1.Requery
   						

   with the following code:

        DataEnvironment1.rsCommand1.Close
        DataEnvironment1.rsCommand1.Open
   					

6. Run the project again. The following output appears in the immediate window:

   LockType Before Requery:  3 
   LockType After Requery:  3
   						

Hierarchical Recordsets Using ADO Objects

1. In Visual Basic 6.0, create a new project of Data Project type.
2. Add a Button control to the default form, frmDataEnv.
3. Double-click the command button to open the Click event handler of the button, and then add the following code to the code window:
   
   Note You must change User ID=<UID> to the correct value before you run this code. User ID <UID> must have the appropriate permissions to perform these operations on the database.

     Dim oCn As New ADODB.Connection
     Dim oRs As New ADODB.Recordset
     
     'Connect to Microsoft SQL Server.
     'oCn.Open "provider=msdatashape;data provider=sqloledb;" & _
               "user id=<UID>;data source=(local);initial catalog=northwind;"
     'Connect to Microsoft Jet 4.0.
     oCn.Open "provider=msdatashape;data provider=microsoft.jet.oledb.4.0;" & _
              "user id=admin;data source=c:\northwind.mdb;"
     'Connect to Microsoft Jet 3.51.
     'oCn.Open "provider=msdatashape;data provider=microsoft.jet.oledb.3.51;" & _
               "user id=admin;data source=c:\nwind.mdb;"
     
     oRs.CursorLocation = adUseClient
     oRs.CursorType = adOpenStatic
     oRs.LockType = adLockBatchOptimistic
     
     'Non-shaped recordset
     'oRs.Open "Select * from Customers", oCn
     'Shaped recordset
     oRs.Open "SHAPE {Select * from Customers}  AS Command1 APPEND ({Select * from Orders}  " & _
              "AS Command2 RELATE 'CustomerID' TO 'CustomerID') AS Command2", oCn
     
     Set oRs.ActiveConnection = Nothing
          
     MsgBox "LockType before Requery is " & oRs.LockType
     
     Set oRs.ActiveConnection = oCn
     
     oRs.Requery
   
     'oRs.Close
     'oRs.Open
     
     MsgBox "LockType after Requery is " & oRs.LockType
   
     oRs.Close
     oCn.Close
     Set oRs = Nothing
     Set oCn = Nothing
   					

4. Modify the connection strings as appropriate for your data source.
5. Run the application, and then click the command button. You receive the following output:

   LockType before Requery is 4     (adLockBatchOptimistic)
   LockType after Requery is 1      (adLockReadOnly)
   						

6. To work around this behavior, comment out the following line of code:

     oRs.Requery
   						

   and uncomment the following two lines of code:

     'oRs.Close
     'oRs.Open
   					

7. Run the project again. You receive the following output:

   LockType before Requery is 4 
   LockType after Requery is 4