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

    'To integrate this code create a DSN called Pubs
    'with a user_id = sa and no password

    Dim Cnxn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim rstTitleAuthor As New ADODB.Recordset
    Dim strCnxn As String
    
        ' open connection with Data Shape attributes
    Set Cnxn = New ADODB.Connection
    strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=;"
    Cnxn.Open strCnxn
    
        ' create recordset
    Set rst = New ADODB.Recordset
    rst.StayInSync = True
    rst.Open "SHAPE {select * from Authors} " & _
                   "APPEND ({select * from titleauthor}" & _
                   "RELATE au_id TO au_id) AS chapTitleAuthor", _
                   Cnxn, , , adCmdText
    
    Set rstTitleAuthor = rst("chapTitleAuthor").Value
    Do Until rst.EOF
       Debug.Print rst!au_fname & " " & rst!au_lname & " " & _
                   rst!State & " " & rst!au_id
       
       Do Until rstTitleAuthor.EOF
          Debug.Print rstTitleAuthor(0) & " " & rstTitleAuthor(1) & " " & _
                   rstTitleAuthor(2) & " " & rstTitleAuthor(3)
       rstTitleAuthor.MoveNext
       Loop
       
    rst.MoveNext
    Loop

     ' clean up
    rst.Close
    Cnxn.Close
    Set rst = Nothing
    Set Cnxn = Nothing
    

End Sub
'EndStayInSyncVB

See Also

Fields Collection | Recordset Object | StayInSync Property

© 1998-2001 Microsoft Corporation. All rights reserved.