NextRecordset Method Example (VB)

This example uses the NextRecordset method to view the data in a recordset that uses a compound command statement made up of three separate SELECT statements.

'BeginNextRecordsetVB

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string

Public Sub Main()
    On Error GoTo ErrorHandler

    ' connection and recordset variables
    Dim rstCompound As ADODB.Recordset
    Dim Cnxn As ADODB.Connection
    Dim strCnxn As String
    Dim SQLCompound As String
    
    Dim intCount As Integer
    
    ' Open connection
    Set Cnxn = New ADODB.Connection
    strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"
    Cnxn.Open strCnxn
    
    ' Open compound recordset
    Set rstCompound = New ADODB.Recordset
    SQLCompound = "SELECT * FROM Authors; " & _
        "SELECT * FROM stores; " & _
        "SELECT * FROM jobs"
    rstCompound.Open SQLCompound, Cnxn, adOpenStatic, adLockReadOnly, adCmdText
    
    ' Display results from each SELECT statement
    intCount = 1
    Do Until rstCompound Is Nothing
        Debug.Print "Contents of recordset #" & intCount
        
        Do Until rstCompound.EOF
            Debug.Print , rstCompound.Fields(0), rstCompound.Fields(1)
            rstCompound.MoveNext
        Loop
        
        Set rstCompound = rstCompound.NextRecordset
        intCount = intCount + 1
    Loop
   
    ' clean up
    Cnxn.Close
    Set rstCompound = Nothing
    Set Cnxn = Nothing
    Exit Sub
    
ErrorHandler:
    ' clean up
    If Not rstCompound Is Nothing Then
        If rstCompound.State = adStateOpen Then rstCompound.Close
    End If
    Set rstCompound = 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
'EndNextRecordsetVB

See Also

NextRecordset Method | Recordset Object