Example: Multiple Recordset Objects for Multiple SQL Statements in a Single Command

Example: Multiple Recordset Objects for Multiple SQL Statements in a Single Command

    Const DS = "MySqlServer"
    Const DB = "Northwind"
    Const DP = "SQLOLEDB"
    
    Dim objRs As New ADODB.Recordset
    Dim CommandText As String
    Dim ConnctionString As String
    
    CommandText = "SELECT OrderID, " & _
                         "ShipName, ShipAddress, ShipCity " & _
                         "FROM Orders " & _
                         "WHERE CustomerID = 'ALFKI' " & _
                         "ORDER BY OrderID"
    CommandText = CommandText & ";" & _
                  "SELECT ContactName, CompanyName, Address, City " & _
                  "FROM Customers " & _
                  "WHERE CustomerID = 'ALFKI' "
                  
    ConnectionString = "Provider=" & DP & _
                       ";Data Source=" & DS & _
                       ";Initial Catalog=" & DB & _
                       ";Integrated Security=SSPI;"
    
    ' Connect to data source and execute the SQL commands.
    objRs.Open CommandText, ConnectionString, _
                adOpenStatic, adLockReadOnly, adCmdText
    
    ' Display the first Recordset.
    Debug.Print "Orders placed by customer with ID=ALFKI"
    Debug.Print vbTab & "ORderID" & _
                vbTab & "ShipName" & _
                vbTab & "ShipAddress" & _
                vbTab & "ShipCity"
    Do While Not objRs.EOF
        Debug.Print vbTab & objRs("OrderID") & _
                    vbTab & objRs("ShipName") & _
                    vbTab & objRs("ShipAddress") & _
                    vbTab & objRs("ShipCity")
        objRs.MoveNext
    Loop
    
    ' Move to display the next Recordset.
    Set objRs = objRs.NextRecordset
    
    Debug.Print "Information about the customer with ID=ALFKI"
    Debug.Print vbTab & "Name" & vbTab & "Addres" & vbTab & "City"
    Do While Not objRs.EOF
        Debug.Print vbTab & objRs("ContactName") & _
                    vbTab & objRs("CompanyName") & _
                    vbTab & objRs("Address") & _
                    vbTab & objRs("City")
        objRs.MoveNext
    Loop
        
    'clean up
    objRs.Close
    Set objRs = Nothing

Output

The result is the following:

Orders placed by customer with ID=ALFKI
    ORderID ShipName                ShipAddress     ShipCity
    10643   Alfreds Futterkiste     Obere Str. 57   Berlin
    10692   Alfred's Futterkiste    Obere Str. 57   Berlin
    10702   Alfred's Futterkiste    Obere Str. 57   Berlin
    10835   Alfred's Futterkiste    Obere Str. 57   Berlin
    10952   Alfred's Futterkiste    Obere Str. 57   Berlin
    11011   Alfred's Futterkiste    Obere Str. 57   Berlin
Information about the customer with ID=ALFKI
    Name           Company               Address         City
    Maria Anders   Alfreds Futterkiste   Obere Str. 57   Berlin