Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
NextRecordset Method Example (VB)
Collapse the table of content
Expand the table of content

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
Show:
© 2015 Microsoft