How To Extract Child Recordsets from Shaped Recordset

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q191744

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).

Back to the top

MORE INFORMATION

Consider the following sample SHAPE statement:

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

           RELATE customerid TO customerid)

The first N columns of the recordset returned correspond to the columns returned by the SQL statement in the first set of brackets after the SHAPE statement. That is, the first N columns will be actual data. After that, a given column in the recordset may be of type adChapter, which indicates a child recordset, or it could be data from a calculated column. (This is not demonstrated in the preceding SQL statement.)

The following Visual Basic sample code demonstrates returning a shaped recordset and a function, PrintTbl, that displays the contents of the returned recordset. Note that the test for the field type of adChapter is to identify child recordsets for a given row.

WARNING: ANY USE BY YOU OF THE CODE PROVIDED IN THIS ARTICLE IS AT YOUR OWN RISK. Microsoft provides this code "as is" without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. Microsoft does not support modifications to this code.

Back to the top

Example

In Visual Basic or Visual Basic for Applications (VBA), create a new project with a Form (Form1) and a Module (Module1).
Add a reference for the Microsoft ActiveX Data Objects Library.

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

Add a Command button to the form (Command1).

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=dsnNwind;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 "dsnNwind" to the name of a ODBC dsn that points to the Nwinds.MDB that comes with Visual Basic. Alternatively, create an ODBC DSN named dsnNwind that points to the Nwinds.MDB that comes with Visual Basic.

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

Back to the top

REFERENCES

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

189657 (http://support.microsoft.com/kb/189657/EN-US/ ) How To Use the ADO SHAPE Command

For a discussion of shaping and other new features in ActiveX Data Objects (ADO), please see the following Web site:

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

Back to the top

APPLIES TO

Microsoft ActiveX Data Objects 2.0
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

Back to the top

kbdatabase kbhowto KB191744

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top