Accessing Rows in a Hierarchical Recordset

The following example shows the steps necessary to access rows in a hierarchical Recordset:

1. Recordset objects from the authors and titleauthors tables are related by author ID.
   
   
2. The outer loop displays each author's first and last name, state, and identification.
   
   
3. The appended Recordset for each row is retrieved from the Fields collection and assigned to rsChapter.
   
   
4. The inner loop displays four fields from each row in the appended Recordset.

Example

Sub datashape()
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim rsChapter As Variant

    cnn.Provider = "MSDataShape"
    cnn.Open    "Data Provider=MSDASQL;" & _
               "DSN=vfox;uid=sa;pwd=vfox;database=pubs”
'STEP 1
    rst.StayInSync = FALSE
    rst.Open    "SHAPE  {select * from authors} 
               APPEND ({select * from titleauthor} AS chapter 
               RELATE au_id TO au_id)", 
               cnn
'STEP 2
    While Not rst.EOF
        Debug.Print    rst("au_fname"), rst("au_lname"), 
                     rst("state"), rst("au_id")
'STEP 3
        Set rsChapter = rst("chapter")
'STEP 4
        While Not rsChapter.EOF
            Debug.Print rsChapter(0), rsChapter(1), 
                        rsChapter(2), rsChapter(3)
            rsChapter.MoveNext
        Wend
        rst.MoveNext
    Wend
End Sub