This example demonstrates how the StayInSync property facilitates accessing rows in a hierarchical Recordset.
The outer loop displays each author's first and last name, state, and identification. The appended Recordset for each row is retrieved from the Fields collection and automatically assigned to rstTitleAuthor by the StayInSync property whenever the parent Recordset moves to a new row. The inner loop displays four fields from each row in the appended recordset.
Public Sub Main()
StayInSyncX
End Sub
Public Sub StayInSyncX()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim rstTitleAuthor As New ADODB.Recordset
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
cnn.Open "DSN=Pubs;Provider=MSDataShape;Data Provider=MSDASQL;"
rst.StayInSync = True
rst.Open "SHAPE {select * from Authors} " & _
"APPEND ({select * from titleauthor}" & _
"RELATE au_id TO au_id) AS chapTitleAuthor", _
cnn, , , adCmdText
Set rstTitleAuthor = rst("chapTitleAuthor").Value
While Not rst.EOF
Debug.Print rst!au_fname & " " & rst!au_lname & " " & _
rst!state & " " & rst!au_id
While Not rstTitleAuthor.EOF
Debug.Print rstTitleAuthor(0) & " " & rstTitleAuthor(1) & " " & _
rstTitleAuthor(2) & " " & rstTitleAuthor(3)
rstTitleAuthor.MoveNext
Wend
rst.MoveNext
Wend
End Sub