Example: Multiple Recordset Objects for One SQL Statement with a COMPUTE Clause

Example: Multiple Recordset Objects for One SQL Statement with a COMPUTE Clause

    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 ProductID, ProductName, UnitPrice " & _
                  "FROM Products " & _
                  "COMPUTE AVG(UnitPrice)"
    
    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 vbTab & "ProductID" & _
                vbTab & "ProductName" & _
                vbTab & "UnitPrice"
    Do While Not objRs.EOF
        Debug.Print vbTab & objRs("ProductID") & _
                    vbTab & objRs("ProductName") & _
                    vbTab & objRs("UnitPrice")
        objRs.MoveNext
    Loop
    
    ' Move to display the next Recordset as the result of
    ' the COMPUTE clause.
    Set objRs = objRs.NextRecordset
    
    Debug.Print "average unit price"
    Debug.Print vbTab & objRs(0)
        
    ' Clean up.
    objRs.Close
    Set objRs = Nothing