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 Main()
    On Error GoTo ErrorHandler

    '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=MSDataShape;Data Provider='sqloledb';Data Source='MySqlServer';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"
    Cnxn.Open strCnxn
    
        ' create recordset
    Set rst = New ADODB.Recordset
    rst.StayInSync = True
    rst.Open "SHAPE {select * from Authors} " & _
                   "APPEND ( { select * from titleauthor} AS chapTitleAuthor " & _
                   "RELATE au_id TO au_id) ", 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
    Exit Sub
    
ErrorHandler:
    ' clean up
    If Not rst Is Nothing Then
        If rst.State = adStateOpen Then rst.Close
    End If
    Set rst = Nothing
    
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
'EndStayInSyncVB

See Also

Fields Collection | Recordset Object | StayInSync Property