NextRecordset Method and DefaultCursorDriver Property Example

This example uses the NextRecordset method to view the data from a compound SELECT query. The DefaultCursorDriver property must be set to dbUseODBCCursor when executing such queries. The NextRecordset method will return True even if some or all of the SELECT statements return zero records; it will return False only after all the individual SQL clauses have been checked.

Sub NextRecordsetX()

    Dim wrkODBC As Workspace
    Dim conPubs As Connection
    Dim rstTemp As Recordset
    Dim intCount As Integer
    Dim booNext As Boolean

    ' Create ODBCDirect Workspace object and open Connection
    ' object. The DefaultCursorDriver setting is required 
    ' when using compound SQL statements.
    Set wrkODBC = CreateWorkspace("", _
        "admin", "", dbUseODBC)
    wrkODBC.DefaultCursorDriver = dbUseODBCCursor
    Set conPubs = wrkODBC.OpenConnection("Publishers", , , _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

    ' Construct compound SELECT statement.
    Set rstTemp = conPubs.OpenRecordset("SELECT * " & _
        "FROM authors; " & _
        "SELECT * FROM stores; " & _
        "SELECT * FROM jobs")

    ' Try printing results from each of the three SELECT 
    ' statements.
    booNext = True
    intCount = 1
    With rstTemp
        Do While booNext
            Debug.Print "Contents of recordset #" & intCount
            Do While Not .EOF
                Debug.Print , .Fields(0), .Fields(1)
                .MoveNext
            Loop
            booNext = .NextRecordset
            Debug.Print "  rstTemp.NextRecordset = " & _
                booNext
            intCount = intCount + 1
        Loop
    End With

    rstTemp.Close
    conPubs.Close
    wrkODBC.Close

End Sub

Another way to accomplish the same task would be to create a prepared statement containing the compound SQL statement. The CacheSize property of the QueryDef object must be set to 1, and the Recordset object must be forward-only and read-only.

Sub NextRecordsetX2()

    Dim wrkODBC As Workspace
    Dim conPubs As Connection
    Dim qdfTemp As QueryDef
    Dim rstTemp As Recordset
    Dim intCount As Integer
    Dim booNext As Boolean

    ' Create ODBCDirect Workspace object and open Connection
    ' object. The DefaultCursorDriver setting is required
    ' when using compound SQL statements.
    Set wrkODBC = CreateWorkspace("", _
        "admin", "", dbUseODBC)
    wrkODBC.DefaultCursorDriver = dbUseODBCCursor
    Set conPubs = wrkODBC.OpenConnection("Publishers", , , _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

    ' Create a temporary stored procedure with a compound
    ' SELECT statement.
    Set qdfTemp = conPubs.CreateQueryDef("", _
        "SELECT * FROM authors; " & _
        "SELECT * FROM stores; " & _
        "SELECT * FROM jobs")
    ' Set CacheSize and open Recordset object with arguments
    ' that will allow access to multiple recordsets.
    qdfTemp.CacheSize = 1
    Set rstTemp = qdfTemp.OpenRecordset(dbOpenForwardOnly, _
        dbReadOnly)

    ' Try printing results from each of the three SELECT
    ' statements.
    booNext = True
    intCount = 1
    With rstTemp
        Do While booNext
            Debug.Print "Contents of recordset #" & intCount
            Do While Not .EOF
                Debug.Print , .Fields(0), .Fields(1)
                .MoveNext
            Loop
            booNext = .NextRecordset
            Debug.Print "  rstTemp.NextRecordset = " & _
                booNext
            intCount = intCount + 1
        Loop
    End With

    rstTemp.Close
    qdfTemp.Close
    conPubs.Close
    wrkODBC.Close

End Sub