Using Multiple Recordsets

One other nice method available for the

Recordset

object is NextRecordset
. So far, we've only talked about a CommandText
property as containing one SQL query. We can provide multiple SQL queries as long as they are separated by semicolons, in the form SELECT
*
FROM
Table1;
SELECT
*
FROM
Table2;
etc.. Normally, when we open a recordset from a query like this, it's only the first set of results that are available. To retrieve the next set of records we just call the NextRecordset
method. We can also supply the name of a variable that will be set to the number of records affected by the query, though this is optional:

Set

recordset =
recordset.NextRecordset(
RecordsAffected)

When we use the

NextRecordset

method, we're provided with a new Recordset
variable that is one of the following:

If a row-returning command returns no records, the Recordset
object referenced by the variable will be empty. We can test for this case by inspecting the BOF
and EOF
properties, which will both be True
.
If a non row-returning command completes successfully, the Recordset
referenced by the variable will be closed. We can test for this case by ensuring that the Recordset
object variable is not equal to Nothing
. In this case, inspecting the EOF
property will generate an error.
When there are no more statements, the Recordset
variable returned will be set to Nothing
.

Note that the

NextRecordset

method will continue to return Recordset
objects as long as there are additional SQL statements in our CommandText
parameter.