Relation-Based Hierarchy

In a relation-based hierarchy, two recordsets are fetched to the client and then related on a common field or fields in the client application. For example, in a scenario relating customers and orders, the orders records for each customer are accessed through a field in the customer recordset that is a recordset in itself.

In the following example, the first command is the most basic of shape commands. It takes two recordsets (defined by the SQL statements in the curly braces) and then relates them on the customerid fields in both recordsets, as follows:

SHAPE  {select * from customers} 
APPEND ({select * from orders} AS rsOrders
RELATE customerid TO customerid) 

This yields the following result:

Customers.*
rsOrders
   |
   +----Orders.*

In the preceding diagram, the parent recordset contains all fields from the Customers table and a field called rsOrders, which provides a reference to the child recordset, which contains all the fields from the Orders table.

The following example executes the preceding SHAPE command and then prints out the results. For this example, the data store has changed to the NWIND Access database included with Microsoft® Visual Studio® and Microsoft Office.

Note   The ADO documentation expands on the examples given here as well as describing the full grammar.

Sub shapetest()

   Dim rst As ADODB.Recordset
   Set rst = New ADODB.Recordset
   strConnect = "Provider=MSDataShape;data _
      provider=msdasql;Data Source=nwind;"

   rst.Source = "shape {select * from customers} APPEND " & _
      "({Select * from orders} As rsOrders " & _
         "RELATE customerid to customerid)"

   rst.ActiveConnection = strConnect
   rst.Open , , adOpenStatic, adLockBatchOptimistic
   printtbl rst, 0

   rst.Close

End Sub

Sub printtbl(rs, indent)
   Dim rsChild As adodb.Recordset

   While rs.EOF <> True
      For Each col In rs.Fields
         If col.Type <> adChapter Then
            Debug.Print Space(indent), col.Value,
         Else
            Debug.Print
            Set rsChild = col.Value
            printtbl rsChild, indent + 4
         End If
      Next
      Debug.Print
      rs.MoveNext
   Wend

End Sub