ADO - ADO NextRecordset Method for Multiple Query Results

The NextRecordset method in ActiveX Data Objects (ADO) is used to move from one Recordset to another when a single command or SQL statement produces multiple result sets. Normally, executing a query returns one Recordset containing rows from one result. However, some database operations can return several result sets in sequence. For example, a stored procedure might execute multiple SELECT statements, with each statement producing its own set of records. The NextRecordset method allows an application to move through these results one by one without executing the database operation again.

Understanding Multiple Result Sets

Consider a stored procedure that contains three queries:

SELECT CustomerID, CustomerName FROM Customers;

SELECT OrderID, OrderDate FROM Orders;

SELECT ProductID, ProductName FROM Products;

When this procedure is executed through ADO, the database can return three Recordsets. The first Recordset contains customer information, the second contains order information, and the third contains product information.

The application initially receives the first Recordset. Calling the NextRecordset method moves the current Recordset reference to the next result.

The basic pattern is:

Set rs = cmd.Execute

Do Until rs Is Nothing

    'Process the current Recordset

    Set rs = rs.NextRecordset

Loop

The application processes the first result, calls NextRecordset, processes the second result, and continues until there are no more results.

Syntax

The basic syntax is:

Set newRecordset = recordset.NextRecordset

Here, recordset represents the current ADO Recordset, while newRecordset receives the next available Recordset.

For example:

Set rs = cmd.Execute

Set rs = rs.NextRecordset

After the second statement, rs refers to the next result set returned by the database.

The method can also be used with an optional records-affected parameter:

Set rs = rs.NextRecordset(recordsAffected)

The RecordsAffected value can provide information about the number of records affected by an operation associated with the result sequence.

Example Using a Stored Procedure

Suppose a database contains a stored procedure called GetCustomerInformation:

CREATE PROCEDURE GetCustomerInformation
AS
BEGIN

    SELECT CustomerID, CustomerName
    FROM Customers;

    SELECT OrderID, CustomerID, OrderDate
    FROM Orders;

END

The procedure produces two result sets. An ADO application can process both results as follows:

Dim conn
Dim cmd
Dim rs

Set conn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")

conn.Open "Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=SalesDB;Integrated Security=SSPI"

Set cmd.ActiveConnection = conn
cmd.CommandText = "GetCustomerInformation"
cmd.CommandType = 4

Set rs = cmd.Execute

Do Until rs Is Nothing

    If Not rs.EOF Then
        Do Until rs.EOF
            'Process fields from the current result
            rs.MoveNext
        Loop
    End If

    Set rs = rs.NextRecordset

Loop

If Not rs Is Nothing Then
    rs.Close
End If

conn.Close

The first iteration processes the customer result. Calling NextRecordset then moves to the orders result. When there are no more results, NextRecordset returns Nothing.

Why NextRecordset Is Useful

The major advantage of NextRecordset is that it allows an application to process multiple database results from a single execution.

Without this functionality, an application might need to execute separate commands for each query. When several related result sets are required, returning them together can simplify application logic and reduce the number of separate database requests.

For example, a reporting application might need:

Result 1: Sales summary
Result 2: Customer details
Result 3: Product information
Result 4: Regional totals

Instead of executing four independent database operations, a stored procedure can return these results together. ADO can then move between them using NextRecordset.

Checking for the End of the Results

A common programming pattern is to continue calling NextRecordset until it returns Nothing.

Set rs = cmd.Execute

Do While Not rs Is Nothing

    'Work with the current result

    Set rs = rs.NextRecordset

Loop

This is important because the application does not necessarily know beforehand how many result sets the database operation will return.

For example, one stored procedure may return two results today and be modified later to return three. Using NextRecordset in a loop allows the application to handle the result sequence without explicitly assuming a fixed number.

Difference Between Recordset and NextRecordset

A Recordset represents a collection of rows returned from a database operation.

NextRecordset does not retrieve the next row. Instead, it moves to the next complete result set.

This distinction is important.

Suppose the database returns:

Result Set 1
----------------
John
Mary
David

Result Set 2
----------------
Order 101
Order 102
Order 103

Using:

rs.MoveNext

moves from John to Mary, and then from Mary to David. It operates within the same Recordset.

Using:

Set rs = rs.NextRecordset

moves from the entire customer result to the order result.

Therefore:

MoveNext       → next row
NextRecordset  → next result set

Handling Empty Result Sets

A result sequence can contain a Recordset with no rows. Therefore, applications should check both whether the Recordset exists and whether it contains records.

For example:

If Not rs Is Nothing Then

    If rs.EOF Then
        'The current result contains no rows
    Else
        'Process the records
    End If

End If

After processing an empty result, the application can still call NextRecordset to continue to the following result.

Important Considerations

NextRecordset depends on the capabilities of the database provider and the type of command being executed. Not every provider supports multiple result sets in exactly the same way. Provider-specific behavior can therefore affect how applications should handle multiple results.

Applications should also properly close Recordsets and database connections after processing is complete. This helps release database and system resources.

It is also important to distinguish NextRecordset from pagination. Pagination retrieves different portions of a larger result, whereas NextRecordset moves between separate results produced by the same database operation.

Advantages

Using NextRecordset provides several practical benefits:

  1. Processes multiple results efficiently: Several related results can be returned from one database operation.

  2. Reduces repeated command execution: The application does not necessarily need to execute a separate command for every result.

  3. Simplifies stored procedure integration: Applications can work with stored procedures that contain multiple SELECT statements.

  4. Supports flexible result processing: The application can process each result according to its own structure.

  5. Improves organization: Related database results can be handled as a single logical operation.

Conclusion

The ADO NextRecordset method provides a way to navigate through multiple Recordsets returned by a single database operation. It is particularly useful when stored procedures or compound database commands produce several independent result sets. The method should not be confused with MoveNext, which moves between individual rows within the current Recordset. NextRecordset moves from one complete result set to another until no additional results are available. Understanding this distinction helps developers build ADO applications that can efficiently process complex database operations and multiple related results.