Microsoft KB Archive/249012

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 12:51, 21 July 2020 by X010 (talk | contribs) (Text replacement - """ to """)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Article ID: 249012

Article Last Modified on 5/8/2003



APPLIES TO

  • Microsoft ActiveX Data Objects 2.1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 2
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft ActiveX Data Objects 2.6
  • Microsoft ActiveX Data Objects 2.7
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.1 Service Pack 1
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7



This article was previously published under Q249012

SYMPTOMS

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

CAUSE

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

RESOLUTION

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

STATUS

This behavior is by design.

MORE INFORMATION

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
                        


Keywords: kbdatabase kbprb KB249012