7.5. Creating ADO Recordsets

ADO recordsets are basically the same as DAO recordsets, with a few notable differences, as explained below.

7.5.1. Creating a Standard Recordset

In DAO you would create a recordset, like this

Set rs = db.OpenRecordset( source, options)

But in ADO, you could do it like this:

rs.Open Source, ConnectionString,
CursorType, LockType, Options

The Source argument can be an SQL statement, the name of a table, the name of a stored procedure, a URL, or a provider-specific text or command. The ConnectionString argument can be a Connection object or a Connection string. Appendix J contains a list of all the available CursorType, LockType, and Options values.

You can supply more than one Options value by using the AND operator, for example:

rs.Options = adCmdStoredProc And adAsyncFetchNonBlocking

7.5.2. Creating a Recordset from a Command Object

Suppose you need to create a recordset that is based on a parameter query. Most often, you won't know what values to supply until you get to that point in your code. The problem is, of course, how to supply those values?

The answer is to base your recordset on a Command object, which itself is based on the Parameter query. How does this solve your dilemma? The following is a typical example.

Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

'Build the Command object
With cmd

    .ActiveConnection = CurrentProject.Connection
    .CommandText = "qryPrices"
    .CommandType = adCmdTable
    .Parameters.Refresh
    .Parameters("City") = strCityVariable
    .Parameters("ProductID") = lngProductID
End With

'Create the Recordset
Set rs = cmd.Execute
MsgBox rs!UnitPrice

rs.Close
Set rs = Nothing
Set cmd = Nothing

Since the Recordset object does not have a Parameters collection, you can see that the Command object is what executes the query and passes its dataset to the Recordset when the Recordset is created. The Command object does have a Parameters collection, so you can supply the query's parameters to it.

If you wanted to pass parameters to a stored procedure in an ADP, you would need to make the following changes:

'Build the Command object
With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "spPrices"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters("@City") = strCityVariable
    .Parameters("@ProductID") = lngProductID
End With

7.5.3. Opening a Shaped Recordset

A very useful feature of ADO is that you can create shaped recordsets. Data shaping allows you to define the columns of a recordset, the relationships between them, and the manner in which the recordset is populated with data. These columns can contain data from a provider, such as Access or the SQL Server, references to another recordset, values derived from a calculation on a row, and so on.

Let's take a simple example. In the NorthwindCS sample database (an ADP), there are two tables, organized into a parent-child relationship: Orders, which contains the header information for customer orders, and Order Details, which contains the individual line items for each order. Figure 7-4 shows this relationship at work.

Let's say you wanted to populate a list with the details of a select set of orders. In the past, you would have created a recordset based on a query very much like the following:

SELECT O.OrderID, O.CustomerID, O.OrderDate,
    D.ProductID, D.UnitPrice, D.Quantity, D.Discount
FROM Orders As O
INNER JOIN [Order Details] As D ON D.OrderID = O.OrderID
WHERE Year(O.OrderDate) = 1996
AND OrderID BETWEEN 10248 AND 10250
ORDER BY O.OrderDate DESC

The above query returns a dataset that contains all the orders in 1996 where the OrderID is between 10248 and 10250, in descending date order. Such a recordset would return the following rows (Figure 7-5).

You'll notice that the columns from the Orders table (OrderID, CustomerID, and OrderDate) are repeated unnecessarily for every row of data returned from the Order Details table. Wouldn't it be nice if you could return only one row of Orders data for each group of related rows from Order Details? Closely examine the following ADO code, paying particular attention to the SQL statement:

Dim cn As New ADODB.Connection
Dim rsOrders As New ADODB.Recordset
Dim rsDetails As New ADODB.Recordset
Dim strSQL As String

'Define and create the connection
cn.CursorLocation = adUseClient

'We have to use this provider
cn.Provider = "MSDataShape"

'Open a connection to SQL Server
cn.Open "Data Provider=SQLOLEDB;" & _
        "Integrated Security=SSPI;Database=NorthwindCS"
Now we need to create the SQL statement that will do all the work.
'Create the SQL statement that does all the work
strSQL = "SHAPE {SELECT DISTINCT OrderID," & _
                                "CustomerID, OrderDate" & _
                "FROM Orders" & _
                "WHERE Year(OrderDate) = 1996" & _
                "AND OrderID BETWEEN 10248 AND 10250" & _
                "ORDER BY OrderDate DESC}" & _
         "APPEND ({SELECT OrderID, ProductID, UnitPrice," & _
                        "Quantity, Discount" & _
                "FROM [Order Details]}" & _
         "RELATE OrderID TO OrderID)"
Once the SQL statement is formed, open a recordset based on it.
'Create the recordset for the orders table
rsOrders.Open strSQL, cn

Do While Not rsOrders.EOF
    'Print out the header rows, one at a time
    Debug.Print rsOrders!OrderID, _
                rsOrders!CustomerID, _
                rsOrders!OrderDate

Now return the child records in a second recordset. If you examine the above SQL statement, you'll see that the child dataset is aliased as Details. The Details column is actually a reference to a child recordset.

'Create the child recordset
    Set rsDetails = rsOrders("Details").Value

    Do While Not rsDetails.EOF
        'Print out the child records, one at a time
        Debug.Print vbTab & rsDetails!ProductID, _
                                rsDetails!UnitPrice, _
                                rsDetails!Quantity, _
                                rsDetails!Discount
        rsDetails.MoveNext
    Loop

    rsOrders.MoveNext
Loop

This is what would be returned by the above code. The obvious difference between the data returned here and what you see in Figure 7-4 is because the CustomerID and ProductID columns are designed using Access lookups, and although Access displays the lookup values, the provider used to shape the recordsets does not.

10250	HANAR		07/08/1996
41	7.7	10	0
51	42.4	35	0.15
65	16.8	15	0.15
10249	TOMSP		07/05/1996
14	18.6	9	0
51	42.4	40	0
10248	VINET		07/04/1996
11	14	12	0
42	9.8	10	0
72	34.8	5	0

When you return the value of a column that contains a reference to another recordset, ADO returns an actual recordset represented by that reference. Recordsets such as this are called hierarchical recordsets. Hierarchical recordsets exhibit a parent-child relationship, in which the parent is the container recordset and the child is the contained recordset. The reference to the child recordset is actually a reference to a subset of the child, called a Chapter. A single parent may reference more than one child recordset.

The Shape statement allows you to create a shaped recordset, which you can then access programmatically or through a visual control. You can issue the Shape statement like any other ADO command text.

This simple example demonstrates only a fraction of what can be accomplished using the Shape statement. Unfortunately, an in-depth examination of SQL is outside the scope of this book; refer to the Access help for more information.

7.5.4. Verifying the Options That a Recordset Supports

Check which options a specific recordset supports by using the Supports method. The method returns True if the option is supported, and False if not. The following table lists the options you can test for.

With this constant . . .	You can do this . . .
AdAddNew	Use the AddNew method to add records.
AdApproxPosition	Use the AbsolutePosition and AbsolutePage properties.
AdBookmark	Use the Bookmark property.
AdDelete	Use the Delete method to delete records.
AdFind	Use the Find method to locate a specific record.
AdHoldRecords	Move the cursor position without committing any changes to the current record.
AdIndex	Use the Index property to set an index.
AdMovePrevious	Use the MoveFirst, MovePrevious, and Move methods to move the cursor position backwards.
AdResync	Use the Resync method to resynchronize the recordset with its underlying data.
AdSeek	Use the Seek method to locate a specific record.
AdUpdate	Use the Update method to commit changes to the current record.
AdUpdateBatch	Use the UpdateBatch and CancelBatch methods.

A typical example of how to test for, say, AbsolutePosition functionality is as follows:

booResult = rs.Supports(adApproxPosition)
MsgBox "This recordset does" & _
        IIf(booResult = True,"", "not") & _
        "support AbsolutePosition and AbsolutePage"

7.5.5. Referring to Recordset Columns

As with DAO recordsets, you can refer to ADO recordset columns in a variety of ways:

rs.Collect(1)
rs.Collect("myField")
rs!myField
rs(1)
rs.Fields(1)
rs.Fields!myField
rs("myField")
rs.Fields("myField")

If you're interested in the relative performance characteristics of each method, refer to the end of Chapter 6.

7.5.6. Filtering and Ordering Recordsets

As with DAO recordsets, you can filter a recordset's output by specifying its source using a WHERE or HAVING clause, or by setting its Filter property. Similarly, setting the ADO recordset's Sort property will change its sort order, just like in DAO.

Since we have already covered these topics in Chapter 6, there is no need to repeat them here.

7.5.7. Navigating Recordsets

SQL queries operate on many records at the same time, but recordsets are designed to allow you to operate on records one-at-a-time. Therefore, to use recordsets effectively, you must be able to navigate from record to record. The following sections describe the various ways in which you can move around in your recordset.

7.5.7.1. RecordCount

You might recall that the RecordCount property in DAO returns the number of rows that the recordset has accessed so far. In ADO, the RecordCount property returns the actual number of rows in the recordset, without first having to force a count by moving to the last row.

For example:

rs.Open "SELECT * FROM Table1", CurrentProject.Connection
If rs.AbsolutePosition > adPosUnknown Then
    'Get the count
    lngCount = rs.RecordCount

    '- - - -
    'Continue processing
    '- - - -

End If

7.5.7.1.1. AbsolutePosition, AbsolutePage

Assuming the provider supports absolute positioning, the AbsolutePosition property allows you to move the cursor to a specific row in the recordset, just as in DAO. For example, if you wanted to move to the 127th row, you could issue the following call:

rs.AbsolutePosition = 127

ADO provides three constants you can use to verify the current cursor position, two of which obviously replace the BOF and EOF properties found in DAO.

• adPosUnknown—The recordset is empty, or the provider doesn't support absolute positioning.

• adPosBOF—True if the current cursor position is before the first record.

• adPosEOF—True if the current cursor position is after the last record.

The ADO-specific AbsolutePage property indicates the page on which the current record resides.

lngCurrentPage = rs.AbsolutePage

You might also recall that the DAO object model provides a PercentPosition property, with which you can move to a relative position in the recordset by specifying a percentage value. ADO does not support this property, but you can accomplish the same thing by calculating the percentage and supplying it to the AbsolutePosition property. For example, if you wanted to move to (roughly) halfway through the recordset, you could do this:

rs.AbsolutePosition = 0.5 * rs.RecordCount

7.5.7.2. MoveFirst, MovePrevious, MoveNext, MoveLast, and Move

These five methods work in exactly the same way in ADO as they do in DAO.

7.5.7.3. Bookmarks and Recordset Clones

Bookmarks and Recordset clones in ADO are exactly the same as in DAO. For more details on using Bookmarks and Recordset clones, refer to Chapter 6.

7.5.8. Finding Records

As you saw in the Chapter 6 section on Bookmarks and Recordset Clones, we often need a way to find a specific record when working with recordsets. DAO provides two ways to find a specific record: Find and Seek.

7.5.8.1. The Seek Method

The ADO Seek method, although a little different from its DAO cousin, is still the fastest way to find a specific record, but it can only be used with server-side cursors on tables that have been opened as adCmdTableDirect, because it specifically relies on the table's indexes (and the indexes reside on the server—not on the client). Naturally, the table must have at least one index for it to search on.

Syntax: rs.Seek KeyValues, SeekOption

To use the ADO Seek method, you must specify three things: the name of the index key to use (although an index can be made up of multiple columns, you can only specify one index at a time), a variant array whose members specify the values to be compared with the key columns, and a SeekEnum constant that defines the kind of Seek to execute. The SeekOption constant can be one of the following.

Constant	Value	Description
AdSeekFirstEQ	1	Locates the first key that is equal to the value specified in KeyValues.
AdSeekLastEQ	2	Locates the last key that is equal to the value specified in KeyValues.
AdSeekAfterEQ	4	Locates the key that is equal to the value specified in KeyValues, or the key just after it.
AdSeekAfter	8	Locates a key that is just after where a match with a value specified in KeyValues would have occurred.
AdSeekBeforeEQ	16	Locates the key that is equal to the value specified in KeyValues, or the key just after where it would have occurred.
AdSeekBefore	32	Locates a key that is just before where a match with a value specified in KeyValues would have occurred.

For example, the following code shows how to search the tblCustomers table to find a customer whose Customer No. is 123:

Set rs = db.OpenRecordset("tblCustomer", dbOpenTable)

rs.Index = "CustomerNo"
rs.Seek 123, adSeekFirstEQ

If rs.EOF Then
    'A matching record was found
Else
    'A matching record was not found
End If

Primary key indexes in Jet databases are called PrimaryKey, whereas primary key indexes in the SQL Server are called PK_tablename by default, but you can name them anything you like. So if you want to use the table's primary key index, you must know its name.

You must specify a key value for each column in the index. The reason is that some of the key fields may default to Null, and since nothing can equal Null, your Seek method will usually not find what you're looking for.

In contrast with the DAO Seek method where you would check the NoMatch property to see if the search succeeded or failed, the ADO Seek method has no such property. If the method finds a record that matches the criteria, the Recordset object's cursor is moved to that row, if not, to the end of the recordset. So if no matching record is found, the Recordset object's EOF property is set to True.

7.5.8.2. The Find Method

Unlike DAO, ADO only has one Find method. The Find method has the following syntax:

rs.Find Criteria, SkipRows, SearchDirection, Start

The Criteria argument can be any valid SQL WHERE clause, without the word WHERE. The SkipRows argument is the number of rows to skip when searching for the next or previous match. The Start argument is a bookmark that you can use as a starting point for the search. And lastly, the SearchDirection argument can be either adSearchForward or adSearchBackward, the function of which is fairly obvious.

Unless otherwise specified, all searches begin at the current row, so it's a good idea to always issue the MoveFirst method before attempting Find when you first open a recordset.

The following code demonstrates how to find the first and second instances of a customer having the word parts in their name.

'Search for the first matching record
rs.Find "[OrgName] LIKE '*parts*'", , adSearchForward
rs.MoveFirst

'Check the result
If rs.NoMatch Then
    MsgBox "Record not found."
Else
    MsgBox "Customer name:" & rs.CustName

    'Search for the next matching record
    rs.Find "[OrgName] LIKE '*parts*'", 1, adSearchForward

    'Check the result
    If rs.NoMatch Then
        MsgBox "Record not found."
    Else
        MsgBox "Customer name:" & rs.CustName
    End If
End If

Notice that the SkipRows argument is specified in both searches. This is because you have to skip the current row too. Unfortunately, you can only specify a single column name in the search criterion. The Find method does not support multicolumn search.

Two interesting points to note are that literal string values can be specified either within single quotes or within hash characters. For example:

"State = 'NY'" or "State = #NY#"

Also, the use of the asterisk as a wildcard character is restricted. You can specify it at the end of the criterion string, or at the beginning AND end. You cannot use the asterisk at the beginning (without one also being at the end), or in the middle. The following truth table illustrates this point.

State LIKE '*York'	Illegal
State LIKE 'New*'	OK
State LIKE '*ew Yor*'	OK
State LIKE 'New *ork'	Illegal

Once a matching record is found, any subsequent search begins from the current cursor position, not from the start or end of the recordset, like the Seek method. As with the Seek method, always follow the search with a check of the recordset's NoMatch property to determine the result of the search.

7.5.9. Editing Data with Recordsets

As in DAO, you edit data in recordsets using the AddNew, Update, and CancelUpdate methods.

You'll notice that we didn't mention the Edit method; that's because it doesn't exist in ADO. In DAO, when you leave a record, any changes are discarded. By contrast, when you leave a record in ADO, the changes are immediately committed. In addition, the ADO Update method is optional. You don't need to use it; however, you'll earn yourself a runtime error if you attempt to close a recordset without committing or cancelling any changes, so I recommend you explicitly use it anyway.

With rs
    .Open "Shippers", cn, _
        adOpenDynamic, adLockOptimistic, adCmdTable

    'Check that a record exists
    If .AbsolutePosition > adPosUnknown Then
        'ADO does not have an "Edit" method
        !Phone = "555-5554"
        .Update
    End If

    'Add a new record
    .AddNew
    !CompanyName = "Ollivanders"
    !Phone = "555-5555"

    If booOK2Save = True Then
        .Update
    Else
        .CancelUpdate
    End If
End With

Using the above technique, you can edit records and send the updates to the database one at a time. Of course, you can edit a bunch of records and send the updates all at once, like

With rs
    .Open "Shippers", cn, _
        adOpenDynamic, adLockOptimistic, adCmdTable

    'Check that a record exists
    If .AbsolutePosition > adPosUnknown Then
        'Edit several records
        !Phone = "555-5554"

        .MoveNext
        !Phone = "666-6666"

.MoveNext
        !Phone "777-7777"
        .Update
    End If
End With

ADO also allows batch updates, which allows you to edit multiple records and then send them all to the OLE DB provider to be saved as a single operation. To use this feature, you must use a client-side cursor and open the recordset using the adLockBatchOptimistic LockType property.

With rs
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockBatchOptimistic
    .Open "Customers", cn

    'Find the right record to edit
    .Find "Country = 'USA'"
    Do While Not .EOF
        'Edit the current record
        !Region = "AA"

        'Skip over the current record to
        'find the next matching record
        .Find "Country = 'USA'", 1
    Loop

    'Commit all the changes
    .UpdateBatch
End With

7.5.10. Persistent Recordsets

In DAO, a recordset exists only within the scope of its object variable, after which it is destroyed. The same can be said of ADO recordsets; however, ADO also provides you with a way to save your recordsets to a file on the disk. This allows you to create a recordset, save it to disk, reopen it at some point in the future, make changes to it, and save it again.

7.5.10.1. Saving a Recordset to a File

To do all this, you use the Recordset object's Save method. The following examples demonstrate how to save, reopen, modify, and then resave a recordset. Not all providers allow you to save a recordset to a file. You're safe with the Jet OLE DB provider, but to be certain with other providers, open the recordset using a client-side cursor.

Dim rs As ADODB.Recordset
Dim strADTGFile As String
Dim strXMLFile As String

Set rs = New ADODB.Recordset

'Open the recordset
rs.CursorLocation = adUseClient

rs.Open "Customers", CurrentProject.Connection, _
adOpenStatic, adLockOptimistic, adCmdTable

'Specify the output files
strADTGFile = "c:TempCustomers.adtg"
strXMLFile = "c:TempCustomers.xml"

You'll get a runtime error if you try to save a recordset to a file that already exists, so we have to delete any existing file first. But if you try to delete a file that doesn't exist, you'll still get a runtime error.

On Error Resume Next
Kill strADTGFile
Kill strXMLFile
Err.Clear
On Error GoTo 0

Now use the Save method to save the recordset to disk. You have two options with regard to file formats: Advanced Data Tablegram (ADTG), which is a proprietary Microsoft format, or the Extensible Markup Language (XML) format.

Saving the recordset in the XML format is great if you intend to exchange data with another application that supports XML, but the ADTG format will produce a smaller file size.

'Save the recordset to disk as an ADT file
rs.Save strADTGFile, adPersistADTG

'Just to show that it can be done, save
'the recordset to disk as an XML file
rs.Save strXMLFile, adPersistXML

'Clean up
rs.Close
Set rs = Nothing
Set cn = Nothing

We'll leave both files on the disk for now, because we haven't finished with them yet.

If we were to continue working with the recordset, adding and deleting rows, or modifying data, the changes would be reflected in the database, not in the file. Any changes you want reflected in the file must be explicitly saved to the file—remember, this recordset is bound to the database by a connection.

7.5.10.2. Creating a Recordset Based on a File

The next example shows you how to reopen the recordset we saved to the disk in the preceding section, make a change to it, then resave it.

Dim rs As ADODB.Recordset
Dim strADTGFile As String

Set rs = New ADODB.Recordset

'Specify the output file
strADTGFile = "c:TempCustomers.adtg"

When you want to open a recordset using a file as its source, you must do so without specifying a connection. This creates a disconnected recordset (which we'll explain a bit later). Once the recordset is open, you can work with it just like any other recordset, but the recordset will be bound to the file—not the database. If you want to bind the recordset to the database, you must then set the recordset's ActiveConnection property.

Our example reconnects to the database, but also resaves the recordset to the file.

'Open the recordset with a client-side cursor,
'but NO connection!
rs.CursorLocation = adUseClient
rs.Open strADTGFile, , adOpenStatic, adLockOptimistic

'Now set the recordset's connection
rs.ActiveConnection = CurrentProject.Connection

'Make a change and save it again
rs!Fax = "555-1234"
rs.Update

Kill strADTGFile
rs.Save strADTGFile, adPersistADTG

'Clean up
rs.Close
Set rs = Nothing

Our final example opens the file again to demonstrate that we have indeed accomplished our goal of saving a modified recordset, after which the two output files are deleted, since we don't need them any more.

Dim rs As ADODB.Recordset
Dim strADTGFile As String
Dim strXMLFile As String

Set rs = New ADODB.Recordset

'Specify the output file
strADTGFile = "c:TempCustomers.adtg"

'Open the recordset with a client-side cursor,
'but NO connection!
rs.CursorLocation = adUseClient
rs.Open strADTGFile, , adOpenStatic, adLockOptimistic

'Now prove that the data had changed since the last operation
Debug.Print rs!Fax

'Clean up
rs.Close
Set rs = Nothing
Kill strADTGFile
Kill strXMLFile

7.5.10.3. Disconnected Recordsets

Ever wanted to use a recordset to store temporary data, but been forced to use a multidimensional array because DAO recordsets are always bound to the database? A disconnected recordset is one that is not bound to a database, file, or other data source. It is completely independent. You can add and delete columns, rows, indexes; all without affecting the data in your database.

To create a disconnected recordset, just open it without a connection.

Dim rs As ADODB.Recordset

'Instantiate the recordset
Set rs = New ADODB.Recordset

'Append some fields
rs.Fields.Append "CustomerID", adInteger
rs.Fields.Append "CustName", adVarChar, 20
rs.Fields.Append "Phone", adVarChar, 15
rs.Fields.Refresh

'Add some data
With rs
.Open
.AddNew
    !CustomerID = 1
    !CustName = "Ollivander"
    !Phone = "555-5555"
.Update
End With

'
'Now do whatever you want with this
'temporary, disconnected recordset
'
'Clean up
rs.Close
Set rs = Nothing

You can also create a disconnected recordset by removing the connection from a bound recordset. For example:

Dim rs As ADODB.Recordset

'Instantiate the recordset
Set rs = New ADODB.Recordset

'Give it a client-side cursor, and set its attributes
rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic
rs.CursorType = adOpenKeyset

'Open the recordset, getting its data from the database
rs.Open "Customers", CurrentProject.Connection

'Now disconnect the recordset
Set rs.ActiveConnection = Nothing

'Print out the data to prove we still have it
Debug.Print rs!CustomerID, rs!CompanyName

'Clean up
rs.Close
Set rs = Nothing

Because the default cursor in ADO is server side, you must use a client-side cursor for this to work, because once you disconnect, there is no server. Any changes you make to the data while the recordset is disconnected will not be reflected in the database until you reconnect it and issue the Update or UpdateBatch methods (depending on how many records you changed).

If you intend to use UpdateBatch, the recordset's LockType must be set to adLockBatchOptimistic, as shown above.

'Change the data
rs!CompanyName = "who cares"

'Reconnect to the data source
rs.ActiveConnection = CurrentProject.Connection
'Update the data
rs.UpdateBatch

'Prove it worked
Debug.Print rs!CustomerID, rs!CompanyName

7.5.10.4. Opening a Recordset Containing More Than One SELECT Query

As in DAO, you can create a recordset containing more than one SELECT query.

The following example demonstrates how to create and use such a recordset. Start by creating a stored procedure to do the job:

CREATE PROCEDURE dbo.MultiSelect AS
    SELECT * FROM Invoices
    SELECT * FROM Customers

or specify a hard-coded query:

strSQL= "SELECT * FROM Invoices SELECT * FROM Customers"

In the example that follows, we have used a stored procedure. You might recall from the same section in Chapter 6 that each SQL statement is separated by a semicolon. As you can see, that's not the case in ADO; just separate the statements by a space (or in the case of a stored procedure, by a line break). Next, we create a procedure to demonstrate how it's done.

Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset

 'Setup the Command object

With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "MultiSelect"
    .CommandType = adCmdStoredProc
End With

'Open the first set of data
Set rs = cmd.Execute

When we create the recordset, the first dataset to be loaded is the one that is specified first in the stored procedure or SQL statement (if hard-coded), and you can cycle through each recordset in the same way you would with other recordsets.

Do While Not rs Is Nothing
    Do While Not rs.EOF
        Debug.Print rs.Fields(0).Name, rs.Fields(0).Value
        rs.MoveNext
    Loop

The Recordset object's NextRecordset method retrieves subsequent sets of data. The recordset is set to Nothing when there are no more recordsets available.

You can terminate a recordset and move on to the next one by issuing the NextRecordset method.

'Open the next set of data
    Set rs = rs.NextRecordset
Loop

'Clean up
'There is no need to close the Recordset object
Set cmd = Nothing

As with other recordsets, you can flush the recordset with the recordset's Cancel method, but remember that this cancels the entire recordset, not just the current dataset.