HOWTO: Extract Child Recordsets from Shaped Recordset

• ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2

SUMMARY

When shaping a hierarchical recordset, examine the Type property of each field object to determine if it is actual data or a child recordset (Type = adChapter).

MORE INFORMATION

Consider the following sample SHAPE statement:

   SHAPE  {SELECT * FROM customers}
   APPEND ({SELECT * FROM orders} AS rsOrders

           RELATE customerid TO customerid) 

Example

1. In Visual Basic or Visual Basic for Applications (VBA), create a new project with a Form (Form1) and a Module (Module1).



2. Add a reference for the Microsoft ActiveX Data Objects Library.



3. Add the following code to the module:

   
      Public Sub PrintTbl(rs, indent)
      Dim s As String, col As ADODB.Field, rsChild As ADODB.Recordset
   
        ' This routine distinguishes between columns in the recordset with
        ' data, i.e. type <> adChapter, and those which contain a child
        ' recordset, for example, type = adChapter.
         Do While Not rs.EOF
   
          s = Space(indent)
          For Each col In rs.Fields
            If col.Type <> adChapter Then
              If Len(s) > indent Then s = s & " | "
              s = s & col.Value
            Else
              ' Display data columns encountered so far (if any).
              If Len(s) > indent Then Debug.Print Space(indent) & s
              ' Recursively call printtbl to display child recordset.
              Set rsChild = col.Value
              PrintTbl rsChild, indent + 4
              ' Reset in case there are further data columns.
              s = Space(indent)
            End If
          Next
   
          ' In case we have any data columns that have not been
          ' displayed yet.
          If Len(s) > indent Then Debug.Print s
   
          rs.MoveNext
        Loop
   
   
      End Sub 




4. Add a Command button to the form (Command1).



5. Add the following code to the form:

   
      Private Sub Command1_Click()
      Dim strConnect, rst As ADODB.Recordset
   
        Set rst = New ADODB.Recordset
   
        strConnect = "Provider=MSDataShape;data provider=msdasql;" _
                 & "data source=AdvWorks;database=nwind;"
   
        rst.Source = "SHAPE {SELECT * FROM customers} APPEND " _
                 & "({SELECT * FROM orders} AS rsOrders " _
                 & "RELATE customerid TO customerid)"
   
        rst.ActiveConnection = strConnect
        rst.Open , , adOpenStatic, adLockBatchOptimistic
   
        debug.print "  PRINTING CUSTOMERS TABLE"
        printtbl rst, 0
   
        Set rst.ActiveConnection = Nothing
        rst.Close
        Set rst = Nothing
   
      End Sub 



NOTE: Make sure that you change the Connect string appropriately for your system. That is, change "AdvWorks" to the name of a ODBC dsn that points to the Nwinds.MDB that comes with Visual Basic. Alternatively, create an ODBC DSN named AdvWorks that points to the Nwinds.MDB that comes with Visual Basic.


6. Run the project, display the form, and click the Command button. Note that the hierarchical data appears in the Immediate or Debug window.

REFERENCES

For additional information about shaping syntax and hierarchical recordsets, please see the following article in the Microsoft Knowledge Base:

Q189657 HOWTO: Use the ADO SHAPE Command

http://msdn.microsoft.com/library/techart/msdn_newado20.htm

Additional query words:

Keywords : kbADO200 kbDatabase kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbMDAC200 kbADO210sp2
Version : WINDOWS:2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbhowto