PRB: Shaped Recordset Returns Incorrect Number of Records

When you open two recordsets with the Msdatashape provider, the second recordset has the incorrect number of records.

You may be inadvertently re-shaping the data in the first recordset.

Change the second SHAPE statement to be within curly brackets { }.

This behavior is by design.

The Msdatashape provider, that ships with Microsoft Data Access Components 2.1 and later, allows the results of a previous Shape statement to be re-shaped. This is often useful when you want to see all the child records at once without having to access them through the parent records.

To re-shape a recordset, specify the Alias name in the new statement without using curly brackets.

Steps to Reproduce Behavior

1. Using Visual Basic 5.0 or 6.0, create a new Standard EXE project.
2. Use the Project and References menu to add a reference to the following type library:
   Microsoft ActiveX Data Objects 2.1 Library
3. Add a Command button (Command1) and the following code to the default form:

   Option Explicit
   
   Private Sub Command1_Click()
   Dim cn As ADODB.Connection, rs As ADODB.Recordset, rs2 As ADODB.Recordset
     Set cn = New ADODB.Connection
     cn.Open "Provider=MSDATASHAPE;Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND.MDB"
     Set rs = New ADODB.Recordset
     rs.CursorLocation = adUseClient
     rs.Open "SHAPE {SELECT * FROM Employees WHERE EmployeeID < 5} AS Employees " & _
             "APPEND ({SELECT * FROM Orders} RELATE EmployeeID TO EmployeeID)", _
             cn, adOpenStatic, adLockReadOnly, adCmdText
     Print_Records rs
     Set rs2 = New ADODB.Recordset
     rs2.Open "SHAPE Employees", cn, adOpenStatic, adLockReadOnly, adCmdText
     Print_Records rs2
     rs.Close
     rs2.Close
   End Sub
   
   Private Sub Print_Records(rs As ADODB.Recordset)
     Debug.Print "Recordset has "; rs.RecordCount; " records."
     rs.MoveFirst
     Do While Not rs.EOF
       Debug.Print rs(0), rs(1), rs(2)
       rs.MoveNext
     Loop
   End Sub
   						

   NOTE: You may have to change the Connect string to correctly point to the Nwind.mdb file.
4. Run the application and click Command. You see the following results:

   Recordset has  4  records.
    1            Davolio       Nancy
    2            Fuller        Andrew
    3            Leverling     Janet
    4            Peacock       Margaret
   Recordset has  4  records.
    1            Davolio       Nancy
    2            Fuller        Andrew
    3            Leverling     Janet
    4            Peacock       Margaret
   					

5. In order to return all of the employees to the second recordset, change the second Shape statement to:

     rs2.Open "SHAPE {SELECT * FROM Employees}", cn, adOpenStatic, adLockReadOnly, adCmdText
   					

6. Re-run the application. The following output is produced:

   Recordset has  4  records.
    1            Davolio       Nancy
    2            Fuller        Andrew
    3            Leverling     Janet
    4            Peacock       Margaret
   Recordset has  9  records.
    1            Davolio       Nancy
    2            Fuller        Andrew
    3            Leverling     Janet
    4            Peacock       Margaret
    5            Buchanan      Steven
    6            Suyama        Michael
    7            King          Robert
    8            Callahan      Laura
    9            Dodsworth     Anne