ADO - Managing Multiple Result Sets with ADO

Introduction

In many database applications, a single database operation needs to retrieve more than one set of records. For example, a dashboard may need customer information, recent orders, and payment history at the same time. Instead of executing three separate database queries, ADO allows developers to execute multiple SQL statements in a single command and retrieve multiple result sets one after another.

Managing multiple result sets improves application performance by reducing the number of database calls, minimizing network traffic, and simplifying data retrieval. ADO provides the NextRecordset method to move from one result set to the next.

What are Multiple Result Sets?

A result set is a collection of records returned by a SQL query.

Normally, a SQL query returns one result set.

Example:

SELECT * FROM Employees;

Result:

EmployeeID EmployeeName Department
101 John Sales
102 David HR
103 Alice Finance

However, a single database command can execute multiple SELECT statements.

Example:

SELECT * FROM Employees;

SELECT * FROM Departments;

SELECT * FROM Projects;

This command produces three separate result sets.

ADO allows the application to access each result set sequentially.

Why Use Multiple Result Sets?

Using multiple result sets offers several advantages.

Instead of:

  1. Open connection

  2. Execute first query

  3. Receive records

  4. Execute second query

  5. Receive records

  6. Execute third query

  7. Receive records

ADO allows:

  1. Open connection

  2. Execute one command

  3. Receive all result sets

This approach reduces communication between the application and the database server.

Working Process

The process involves the following steps.

Step 1: Open Database Connection

The application connects to the database.

Application
      |
Database Connection

Step 2: Execute Multiple Queries

Example:

SELECT * FROM Customers;

SELECT * FROM Orders;

SELECT * FROM Payments;

Step 3: Receive First Result Set

ADO initially loads only the first result set.

Customers

Step 4: Move to Next Result Set

Use the NextRecordset method.

Set rs = rs.NextRecordset

ADO now loads:

Orders

Step 5: Continue Until No More Result Sets Exist

The process continues until all result sets have been processed.

Customers
     ↓
Orders
     ↓
Payments
     ↓
No More Result Sets

Creating Multiple Result Sets

Example SQL:

SELECT EmployeeID, EmployeeName FROM Employees;

SELECT DepartmentID, DepartmentName FROM Departments;

SELECT ProjectID, ProjectName FROM Projects;

This returns three result sets.

Executing Multiple Queries in ADO

Example:

Dim con As New ADODB.Connection
Dim rs As ADODB.Recordset

con.Open ConnectionString

Set rs = con.Execute( _
"SELECT * FROM Employees; " & _
"SELECT * FROM Departments; " & _
"SELECT * FROM Projects")

The first Recordset contains employee data.

Reading the First Result Set

Example:

Do Until rs.EOF

    Debug.Print rs("EmployeeName")

    rs.MoveNext

Loop

This processes only the Employees table.

Moving to the Second Result Set

Example:

Set rs = rs.NextRecordset

Now the Recordset contains department data.

Example:

Do Until rs.EOF

    Debug.Print rs("DepartmentName")

    rs.MoveNext

Loop

Moving to the Third Result Set

Example:

Set rs = rs.NextRecordset

Now the Recordset contains project information.

Example:

Do Until rs.EOF

    Debug.Print rs("ProjectName")

    rs.MoveNext

Loop

Detecting the End of Result Sets

Eventually, no additional Recordsets remain.

Example:

Set rs = rs.NextRecordset

If rs Is Nothing Then

    MsgBox "No More Result Sets"

End If

This prevents attempts to access non-existent data.

Using Stored Procedures

Stored procedures frequently return multiple result sets.

Example SQL Server stored procedure:

CREATE PROCEDURE EmployeeDashboard

AS

SELECT * FROM Employees;

SELECT * FROM Departments;

SELECT * FROM Projects;

ADO can retrieve all three Recordsets from one stored procedure execution.

Example:

Set rs = con.Execute("EmployeeDashboard")

Processing Each Result Set

Example:

Do

    While Not rs.EOF

        Debug.Print rs.Fields(0)

        rs.MoveNext

    Wend

    Set rs = rs.NextRecordset

Loop Until rs Is Nothing

This loop automatically processes every result set returned by the database.

Real-World Example

Suppose a company dashboard displays:

  • Employee list

  • Department list

  • Salary details

  • Attendance records

Instead of sending four separate SQL queries:

Query 1

↓

Query 2

↓

Query 3

↓

Query 4

One database call returns:

Employees

↓

Departments

↓

Salaries

↓

Attendance

The application retrieves each Recordset using NextRecordset.

Benefits

Faster Performance

Only one request is sent to the database.

Reduced Network Traffic

Fewer database requests reduce communication overhead.

Better Resource Utilization

Database connections remain active for less time.

Simplified Coding

One database call replaces multiple individual queries.

Efficient Dashboards

Applications that display multiple sections of information can retrieve all required data in a single operation.

Common Applications

Employee Management Systems

Retrieve:

  • Employee details

  • Departments

  • Job positions

  • Payroll data

using one stored procedure.

Hospital Management

Retrieve:

  • Patient information

  • Doctor details

  • Appointment schedule

  • Medical history

in a single database call.

Banking Systems

Retrieve:

  • Customer details

  • Account information

  • Transactions

  • Loan records

simultaneously.

E-commerce Applications

Retrieve:

  • Customer profile

  • Orders

  • Shopping cart

  • Product recommendations

using one database request.

School Management

Retrieve:

  • Student records

  • Subject list

  • Attendance

  • Examination results

through one stored procedure.

Best Practices

  • Ensure each query returns the intended columns and data.

  • Process the current Recordset completely before calling NextRecordset.

  • Check whether the returned Recordset is Nothing before accessing it.

  • Use stored procedures when multiple related result sets are required.

  • Close the Recordset and database connection after processing all results.

  • Handle errors that may occur if one of the queries fails.

  • Avoid returning unnecessary result sets to reduce memory usage.

Limitations

  • Not all database providers support multiple result sets.

  • Very large result sets can consume significant memory.

  • Developers must process Recordsets in sequence; random access to later result sets is not possible until the current one is handled.

  • If one query in the batch fails, error handling becomes more important to ensure proper cleanup.

Difference Between Single and Multiple Result Sets

Feature Single Result Set Multiple Result Sets
Number of queries One Multiple
Database calls One per query One for all queries
Network traffic Higher when many queries are needed Lower
Performance Suitable for simple operations Better for related data retrieval
Method used Standard Recordset NextRecordset
Typical use case Displaying one table Dashboards, reports, and stored procedures

Conclusion

Managing multiple result sets in ADO is an efficient technique for retrieving several related sets of data through a single database request. By executing multiple SQL statements or stored procedures and navigating each returned Recordset with the NextRecordset method, applications can reduce database communication, improve performance, and simplify code. This feature is particularly valuable in enterprise applications, reporting systems, dashboards, banking software, hospital management systems, and e-commerce platforms where multiple categories of information must be displayed together efficiently.