StayInSync Property Example (VB)

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.

'BeginStayInSyncVB
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
'EndStayInSyncVB

See Also

Fields Collection | Recordset Object | StayInSync Property