NextRecordset Method

Applies To

Dynamic-Type Recordset object, Dynaset-Type Recordset object, Forward-Only–Type Recordset object, Recordset object, Snapshot-Type Recordset object.

Description

Gets the next set of records, if any, returned by a multi-part select query in an OpenRecordset call, and returns a Boolean value indicating whether one or more additional records are pending (ODBCDirect workspaces only).

Syntax

Set boolean = recordset.NextRecordset

The NextRecordset method syntax has these parts.

Part	Description

boolean	A Boolean variable. True indicates the next set of records is available in recordset; False indicates that no more records are pending and recordset is now empty.
recordset	An existing Recordset variable to which you want to return pending records.

Remarks

In an ODBCDirect workspace, you can open a Recordset containing more than one select query in the source argument of OpenRecordset, or the SQL property of a select query QueryDef object, as in the following example.

SELECT LastName, FirstName FROM Authors
WHERE LastName = 'Smith';
SELECT Title, ISBN FROM Titles
WHERE Pub_ID = 9999

The returned Recordset will open with the results of the first query. To obtain the result sets of records from subsequent queries, use the NextRecordset method.

If more records are available (that is, there was another select query in the OpenRecordset call or in the SQL property), the records returned from the next query will be loaded into the Recordset, and NextRecordset will return True, indicating that the records are available. When no more records are available (that is, results of the last select query have been loaded into the Recordset), then NextRecordset will return False, and the Recordset will be empty.

You can also use the Cancel method to flush the contents of a Recordset. However, Cancel also flushes any additional records not yet loaded.

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