ADO - ADO NextRecordset Method

The NextRecordset method in ADO is used to move from one result set to the next result set returned by a single database command. Normally, when a SQL query returns data, an ADO Recordset contains that particular result. However, a stored procedure or a batch of SQL statements can return multiple result sets. In such situations, NextRecordset allows an application to process each result set sequentially without executing a new database command for every result.

Why NextRecordset Is Needed

Consider a stored procedure that executes three SELECT statements:

SELECT * FROM Customers;

SELECT * FROM Orders;

SELECT * FROM Products;

The database can return three separate result sets from this single execution. The first result set contains customer information, the second contains order information, and the third contains product information.

When the command is executed through ADO, the initial Recordset represents the first result set. After processing it, the application can call NextRecordset to access the second result set.

The process is:

Execute Command
      |
      v
First Recordset
      |
      | NextRecordset
      v
Second Recordset
      |
      | NextRecordset
      v
Third Recordset

This approach is useful because the database operation is performed once while multiple results can be retrieved and processed.

Syntax

The basic syntax is:

Set recordset = recordset.NextRecordset(RecordsAffected)

NextRecordset returns another Recordset object representing the next available result set.

The optional RecordsAffected parameter can receive the number of records affected by an operation.

For example:

Set rs = rs.NextRecordset

After this statement, rs refers to the next result set.

Example Using Multiple SELECT Statements

Suppose the database contains three tables:

Customers
Orders
Products

A SQL batch can be written as:

SELECT CustomerID, CustomerName
FROM Customers;

SELECT OrderID, CustomerID, OrderDate
FROM Orders;

SELECT ProductID, ProductName, Price
FROM Products;

The ADO code could be:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=SalesDB;Integrated Security=SSPI;"

Set rs = New ADODB.Recordset

rs.Open "SELECT CustomerID, CustomerName FROM Customers;" & _
        "SELECT OrderID, CustomerID, OrderDate FROM Orders;" & _
        "SELECT ProductID, ProductName, Price FROM Products;", _
        cn

Do While Not rs Is Nothing

    If Not rs.EOF Then
        Do While Not rs.EOF
            Debug.Print rs.Fields(0).Value
            rs.MoveNext
        Loop
    End If

    Set rs = rs.NextRecordset

Loop

Here, the first Recordset contains customers. Calling NextRecordset moves to the orders result. Calling it again moves to the products result.

When there are no more result sets, NextRecordset returns Nothing.

Working with Stored Procedures

NextRecordset is particularly useful when working with stored procedures that return multiple result sets.

For example:

CREATE PROCEDURE GetSalesInformation
AS
BEGIN

    SELECT CustomerID, CustomerName
    FROM Customers;

    SELECT OrderID, CustomerID, OrderDate
    FROM Orders;

END

The stored procedure returns two result sets.

The first result set contains customers:

CustomerID    CustomerName
1             John
2             David
3             Robert

The second result set contains orders:

OrderID    CustomerID    OrderDate
101        1             2026-08-01
102        2             2026-08-03
103        1             2026-08-05

After processing the customer records, NextRecordset can be used to obtain the orders.

Processing Different Result Sets

A common pattern is:

Set rs = command.Execute

Do Until rs Is Nothing

    If rs.State = adStateOpen Then

        If Not rs.EOF Then

            Do Until rs.EOF

                'Process current record

                rs.MoveNext
            Loop

        End If

    End If

    Set rs = rs.NextRecordset

Loop

This structure allows the application to continue processing until there are no more result sets.

Empty Result Sets

A result set does not necessarily have to contain records.

For example:

SELECT * FROM Customers WHERE CustomerID = -1;

SELECT * FROM Orders;

The first query may return an empty Recordset.

The application can still call NextRecordset to move to the second result set.

Therefore, applications should distinguish between:

Recordset exists but contains no records

and:

No more Recordsets exist

The first situation can be checked with EOF, while the second is indicated when NextRecordset returns Nothing.

Difference Between MoveNext and NextRecordset

These two methods perform completely different operations.

MoveNext moves to the next record within the same Recordset.

rs.MoveNext

For example:

Record 1
   |
MoveNext
   v
Record 2
   |
MoveNext
   v
Record 3

NextRecordset moves to the next result set.

Set rs = rs.NextRecordset

For example:

Customer Result Set
        |
        | NextRecordset
        v
Order Result Set
        |
        | NextRecordset
        v
Product Result Set

Therefore:

MoveNext       = next record
NextRecordset  = next result set

Difference Between Multiple Queries and NextRecordset

Without NextRecordset, an application might execute three separate database commands:

Execute Query 1
Execute Query 2
Execute Query 3

With multiple result sets, the database can execute a single command:

Execute One Command
        |
        +--> Result Set 1
        |
        +--> Result Set 2
        |
        +--> Result Set 3

The application can then navigate between them using NextRecordset.

This can make related data retrieval more organized and, depending on the application and database workload, reduce the number of separate database round trips.

Important Considerations

NextRecordset should be used carefully when working with large amounts of data. Multiple result sets can consume considerable memory and resources, particularly when they contain many records.

The application should also properly close Recordsets and connections when they are no longer required:

If Not rs Is Nothing Then
    If rs.State = adStateOpen Then rs.Close
End If

If Not cn Is Nothing Then
    If cn.State = adStateOpen Then cn.Close
End If

Set rs = Nothing
Set cn = Nothing

It is also important to process or release the current result set appropriately before moving to the next one.

Advantages of NextRecordset

The main advantages include:

  1. It allows multiple result sets to be processed from one command.

  2. It is useful with stored procedures that return multiple queries.

  3. It can reduce the need for separate command executions.

  4. It provides a convenient way to process related datasets sequentially.

  5. It separates multiple logical results while keeping them associated with one database operation.

  6. It is useful for reporting applications that need several related datasets.

Practical Example

Imagine an employee management application that needs to display three sections on a dashboard:

Employee Information
Department Information
Salary Information

Instead of executing three separate database commands, a stored procedure could return all three result sets:

SELECT * FROM Employees;

SELECT * FROM Departments;

SELECT * FROM Salaries;

ADO receives the first result set:

Set rs = cmd.Execute

The application processes employees:

Do While Not rs.EOF
    'Process employee
    rs.MoveNext
Loop

It then moves to departments:

Set rs = rs.NextRecordset

After processing departments, it moves to salaries:

Set rs = rs.NextRecordset

Finally, when no additional result sets are available:

Set rs = rs.NextRecordset

returns Nothing.

Thus, NextRecordset provides a simple mechanism for moving through multiple results produced by one database operation.

Summary

The ADO NextRecordset method is used when a single database command produces multiple result sets. The first result set is available through the initial Recordset, and NextRecordset moves the application to subsequent results. It is especially useful with stored procedures and SQL batches containing multiple SELECT statements.

The most important distinction to remember is:

MoveNext       → moves between records
NextRecordset  → moves between result sets

When there are no additional result sets, NextRecordset returns Nothing. This makes it possible to use a loop to process all available results sequentially.