ADO Recordset Object Functions and Methods

[Previous] [Next]

Now let's take a closer look at the functions and methods available on the Recordset object. The following table offers a brief description of each available function and method.

Recordset Object Functions and Methods
Function or Method Name	Description
AddNew	Adds a new record to your Recordset
Cancel	Cancels an asynchronous query
CancelBatch	Cancels pending changes in a Recordset that uses batch optimistic updates
CancelUpdate	Cancels pending changes on a record currently being edited
Clone	Creates a new reference to your Recordset that allows independent navigation
Close	Closes the Recordset object, releasing its contents
CompareBookmarks	Compares two bookmarks in the same Recordset
Delete	Deletes the current record from your Recordset
Find	Searches your Recordset for a record based on a string criteria
GetRows	Returns data from your Recordset in a two-dimensional Variant array
GetString	Returns data from your Recordset in a string
Move	Moves the position of the current record in your Recordset
MoveFirst	Moves to the first record in your Recordset
MoveLast	Moves to the last record in your Recordset
MoveNext	Moves to the next record in your Recordset
MovePrevious	Moves to the previous record in your Recordset
NextRecordset	Retrieves the results of the next query in your batch query
Open	Opens the Recordset
Requery	Reexecutes the query that generated the Recordset
Resync	Retrieves the current data for the records in the Recordset from your database
Save	Writes the contents of the Recordset to a file
Seek	Searches the Recordset for a specified string
Supports	Specifies whether the Recordset supports a particular type of functionality
Update	Writes pending changes to the Recordset
UpdateBatch	Submits pending changes in a Recordset that uses batch optimistic updating to the database

AddNew Method

Use the AddNew method to insert new data into the Recordset. This method accepts the following optional parameters:

• FieldList This parameter accepts a Variant array containing the names of the fields you want to populate.
• Values This parameter accepts a Variant array containing the values for the fields you want to populate.

There are two ways to use this method. You can call AddNew, populate the Value property of the desired Field objects, and then call the Update method:

 rsCustomers.AddNew rsCustomers.Fields("CustomerID").Value = "NewID" rsCustomers.Fields("CompanyName") = "New Customer" rsCustomers.Fields("Address").Value = "23 Highview St." rsCustomers.Fields("City") = "Westwood" rsCustomers.Fields("State") = "MA" rsCustomers.Fields("Zip") = "02090" rsCustomers.Update 

You can also perform all these actions in a single function call using the two optional parameters on the AddNew method, each of which accepts a Variant array. The first Variant array contains the names of the fields to populate, and the second contains the values to insert into those fields.

 varFields = Array("CustomerID", "CompanyName", _ "Address", "City", "State", "Zip") varValues = Array("NewID", "New Customer", _ "23 Highview St.", "Westwood", _ "MA", "02090") rsCustomers.AddNew varFields, varValues 

If you're in immediate update mode (with a LockType of adLockOptimistic or adLockPessimistic), the provider will attempt to write the new record to the database as soon as you have completed the insertion (immediately after the call to Update in the first example, and after the call to AddNew in the second example). If you're using batch optimistic updating, this newly inserted record will be marked as a pending insert in the Recordset (with a Status value of adRecNew) until you call the UpdateBatch method to submit the changes to the database.

Cancel Method

The Cancel method allows you to terminate the execution of an asynchronous query. Although I've used this feature with server-side Recordsets, I've yet to successfully use it on a client-side Recordset. Before relying on this feature in your application, be sure to test the State property on the Recordset before (to see if the Recordset is open) and after (to see if the Recordset is closed) issuing this method to ensure that Cancel is behaving as expected.

CancelBatch Method

Use the CancelBatch method when you want to cancel pending batch changes in an optimistically locked Recordset. The CancelBatch method has one parameter, which controls the records in your Recordset for which you want to roll back changes:

• AffectRecords Controls which records will be affected by the CancelBatch method. This optional parameter takes its value from AffectEnum. See the sidebar for more information on the behavior associated with each constant.

AffectEnum Values
Constant	Value	Description
adAffectCurrent	1	Affects only the current record.
adAffectGroup	2	Affects only the records currently visible based on whether the Filter property is set to an array of bookmarks or to a value in FilterGroupEnum other than adFilterNone.
adAffectAll	3	Default, but hidden in the type library; affects all records in the Recordset, except for other levels of a hierarchy or other chapters at the same level. If a string-based filter is used, only those records visible through the filter will be affected.
adAffectAllChapters	4	Affects all chapters at the same level of the Recordset, regardless of the current filter.

The Strange Tale of AffectEnum

The ADO development team devised a wonderful way to control the records that a method such as UpdateBatch affects: they offered the optional parameter AffectRecords. Depending on the value you specify for this parameter, the method will affect only the current record, all the currently visible records, and so on. To me, this is much more elegant than providing separate methods such as UpdateBatchCurrent or UpdateBatchAll. Simply provide a value from the AffectEnum enumeration in the AffectRecords parameter, and voila!

For several reasons, which I'll try to explain without performing a "career-limiting move," the behavior associated with some of the constants is somewhat counter-intuitive.

The behavior associated with the adAffectCurrent value is simple to understand. Use this constant when you want to affect only the current record. Things get more complex when explaining the behavior of the rest of the constants.

Different types of filters exist. The first type is the one that most programmers traditionally envision—one such as rs.Filter = "Country = 'Germany'". I refer to this type of filter as string-based because we're setting the Filter property to a string. As we discussed earlier in the chapter, the Filter property also accepts an array of bookmarks and any of the visible constants from FilterGroupEnum. Internally, ADO handles the string-based filters differently than the other filters.

The adAffectGroup constant affects the currently visible records, but only if the Recordset's filter property has been set to an array of bookmarks or to one of the constants in FilterGroupEnum other than adFilterNone. If you have either no filter or a string-based filter and you use adAffectGroup as the value in the AffectRecords parameter, you'll receive a trappable error.

If you use the adAffectAll constant, you'll affect all records in the Recordset. Sort of. If the Recordset's Filter property is set to a string-based filter, only the records visible through the filter will be affected. (Prior to ADO 2.0, adAffectAll affected all records when a string-based filter was in use, including those not visible through the filter.) However, if you're using an array of bookmarks or one of the values from FilterGroupEnum, you'll affect all records, including those that aren't visible through the filter. Also, if you're using a hierarchical Recordset, only the currently visible records will be affected if you use adAffectAll. I'm not sure why the adAffectAll constant is hidden in the type library, but it works just fine and in many cases is the default value for the AffectRecords parameter.

We'll talk about hierarchies in more depth in Chapter 14. Until then, this quick overview of how hierarchies and AffectEnum work should suffice. Hierarchies contain elements called "chapters." In a customers/orders hierarchy, a chapter in the orders level of the hierarchy represents the orders associated with the current customer. If you want to affect only the current chapter, use the adAffectAll constant. But if you want to affect all chapters in the current level of the hierarchy, use adAffectAllChapters.

Say you've modified all the customers and all the orders in the hierarchy. If you call UpdateBatch on the orders Recordset and use adAffectAllChapters, you'll submit pending changes for all orders—but you won't submit any of the pending changes in the customers Recordset. If you use adAffectAll instead of adAffectAllChapters, UpdateBatch will affect only the orders for the current customer.

One last note. Even if you're not using a hierarchical Recordset, adAffectAllChapters might come in handy. If you have a string-based filter on a normal, nonhierarchical Recordset but you want to affect all the records rather than only those visible through the filter, use the adAffectAllChapters constant.

If this seems confusing to you, join the club. It took me a long time to feel like I understood the behavior associated with each constant. The behavior is confusing, but it works.

CancelUpdate Method

The CancelUpdate method is similar to the CancelBatch method in that it's used to cancel changes made to the Recordset. The CancelUpdate method, however, cancels changes for only the current record and is designed for use while the record is being edited. Say you're programmatically adding or modifying a record in optimistic updating mode and you decide that you no longer want to make those changes to the record. If you call the CancelUpdate method prior to calling Update on the Recordset, the changes you've made will be canceled.

If you find that the CancelUpdate method isn't having the effect you anticipated, be sure to check the EditMode property prior to calling CancelUpdate. Some bound controls implicitly call the Update method on the Recordset, so the CancelUpdate method can't undo those changes. Contrary to what's stated in the product documentation, the CancelUpdate method will not generate an error if the current record has not been edited.

Clone Method

There may be times when you want to maintain two separate references to the same Recordset. For example, you might want to display two separate grids that contain the same data on the same form at the same time and allow the user to navigate through the grids separately. The Clone method is designed to handle this type of scenario.

You can call the Clone method to generate a new Recordset object variable. The one parameter on the method can be used to set the LockType of the new Recordset object variable:

• LockType Controls the LockType for the new Recordset object. This optional parameter accepts a value from LockTypeEnum.

By default, the new Recordset object variable will be created with a LockType value of adLockUnspecified, meaning the clone will receive the same value for the LockType property as the value the original Recordset object variable received. You can also use the LockType parameter to request that the new Recordset object variable be read-only by setting the parameter to adLockReadOnly.

This method is available only for Recordsets that support bookmarks. The bookmarks are interchangeable between a Recordset and its clones. Attempts to use the Clone method on a Recordset that does not support bookmarks (forward-only or dynamic cursors) will generate an error.

Cloned Recordset object variables refer to the same data in memory. Changes made in one Recordset object variable are visible in other variables that refer to the same Recordset. When you're done using a clone of a Recordset, you can call the Close method without affecting the original Recordset or the other clones.

Close Method

Speaking of the Close method… Calling the Close method on the Recordset object releases the Recordset's resources. If you have multiple references to the same Recordset object, the Close method will close all references to that Recordset unless you used the Clone method to obtain those references. This is demonstrated in the following code. When the Close method is called on the rsOriginal object, rsOriginal and rsDuplicate are closed, but rsClone remains open.

 rsOriginal.Open strSQL, cnDatabase, adOpenStatic Set rsDuplicate = rsOriginal Set rsClone = rsOriginal.Clone rsOriginal.Close 

If you have multiple references to the same Recordset object and don't want to use the Clone method, don't use the Close method to release your object variable. Instead Visual Basic users can simply let the object variable go out of scope or explicitly set the object variable to Nothing. Setting the last object variable referencing a Recordset to Nothing will implicitly close the Recordset object.

CompareBookmarks Method

If you have the values of two bookmarks, you can use the CompareBookmarks method to compare them. This method takes two parameters—the bookmarks you're comparing—and returns a value from CompareEnum, as described in the table that follows.

• Bookmark1 This parameter accepts a CompareEnum data type constant. It is the first bookmark to compare.
• Bookmark2 This parameter accepts a CompareEnum data type constant and is the second bookmark to compare.

CompareEnum Values
Constant	Value	Description
adCompareLessThan	0	The first bookmark is before the second.
adCompareEqual	1	The bookmarks are equal.
adCompareGreaterThan	2	The first bookmark is after the second.
adCompareNotEqual	3	The bookmarks are not equal.
adCompareNotComparable	4	The bookmarks cannot be compared.

There are some ground rules for using this method. First, the Recordset must support bookmarks. Second, you should compare bookmark values from the same Recordset object or its clone. There is no basis of comparison for examining bookmarks from different Recordsets, even if they're two Recordsets based on the same query string. For example, client-side Recordset bookmarks are usually equal to the AbsolutePosition property for that record. If you run the same query twice, there is no guarantee that you'll retrieve the same data: records might have been added, modified, or deleted between the execution of your queries. As a result, even if you're looking at the nth row of each Recordset, you're not guaranteed that those two rows contain the same data.

I mentioned that client-side Recordsets generally use bookmarks that correspond to the record number in the Recordset. This allows ADO to determine which of the two bookmarks appears first in the Recordset if the two bookmarks are not equal. However, there is no requirement that bookmarks be ordered by record number. As a result, you might find that comparing two unequal bookmarks returns adCompareNotEqual, rather than adCompareGreaterThan or adCompareLessThan.

It is worth repeating that you should not compare bookmarks from different Recordset objects (unless they're clones of each other), even if the Recordsets are based on the same query string. If either bookmark you pass into the CompareBookmarks method does not correspond to a record in the Recordset object whose CompareBookmarks method is called, you'll receive a run-time error. However, if you open two client-side Recordsets based on different query strings and compare the bookmarks for the initial record in each Recordset, the CompareBookmarks method will return adCompareEqual. Why? The bookmarks both contain the value -1.

Delete Method

Use the Delete method to delete records from your Recordset. The Delete method has one parameter:

• AffectRecords This parameter controls which records will be affected by the Delete method. It takes its value from AffectEnum and is optional.

As of ADO 2.5, the only value for AffectRecords that I've been able to use successfully is the default—adAffectCurrent. (Why create an optional parameter that accepts no value other than the default? Your guess is as good as mine.) This feature might be more thoroughly implemented in a future release. Until then, if you want to delete more than a single record at a time you can use the Delete method within a loop.

After you call the Delete method, the deleted record remains current until you move to the next available record. Unless you're using client-side batch optimistic updating, the deleted record is no longer available. Attempting to reference this record will generate an error.

If you're using client-side batch optimistic updating, the record is deleted only within the Recordset until you call the UpdateBatch method. You can locate pending deletions such as these by saving bookmark values prior to deleting records. You can also use the Filter property on the Recordset by setting it to adFilterPendingRecords and searching for records with a Status property value of adRecDeleted. Once you've located a pending deletion, you can check the OriginalValue property on each Field object to determine which record you deleted. (See the section "ADO Field Object Properties")

Find Method

You can use the Find method on the Recordset to locate a record based on a search string. This feature works with client-side and server-side Recordsets, but only if the Recordset supports bookmarks (static or keyset). If the Find method is called on a server-side Recordset, ADO walks through the contents of the Recordset to locate the appropriate record. With client-side Recordsets, ADO builds an internal index on the field referenced in the search criteria. This method accepts the following parameters:

• Criteria This parameter accepts a string data type. The criteria specifies the field name, value, and operator used to search your Recordset.
• SkipRecords This parameter accepts a long data type. It controls how many records to skip before beginning your search and is optional.
• SearchDirection This parameter takes its value from SearchDirectionEnum and is optional. It controls the direction in which ADO will search your Recordset.
• Start This parameter takes a Variant data type and is optional. It accepts a bookmark to specify where in your Recordset the search will begin.

The Criteria parameter is a string—the search criteria. You can specify one field and one value in the string. You can compare the field and the value with these operators: =, <, >, <=, >=, <>, or LIKE. If you use the LIKE operator, you can use a wildcard (*, %, or _) either at the end of the string value or at the beginning and end of the string value. If no records satisfy the criteria, the Recordset will point to BOF or EOF.

You can use the second parameter, SkipRecords, to specify how many records you want to skip relative to the current record. This optional parameter accepts a long value, which defaults to 0, and can prove handy if you don't want to include the current record in your search. For example, if you want to loop through all the customers from Germany, you could use code such as the following:

 strCriteria = "Country = 'Germany'" rsCustomers.Find strCriteria Do While Not rsCustomers.EOF rsCustomers.Find strCriteria, 1 Loop 

Of course, if this was your goal, you could have used the Filter property instead.

The third parameter, SearchDirection, is also optional and accepts one of the two SearchDirectionEnum constants shown in the following table.

SearchDirectionEnum Values
Constant	Value	Description
adSearchForward	1	Default; searches forward through the Recordset from the record specified
adSearchBackward	-1	Searches backward through the Recordset from the record specified

The final parameter on the method, Start, accepts a Variant and is used to control where the search starts. This optional parameter can accept a bookmark or one of the BookmarkEnum constants shown in this next table.

BookmarkEnum Values
Constant	Value	Description
adBookmarkCurrent	0	Default; begins the search at the current record
adBookmarkFirst	1	Begins the search at the first record in the Recordset
adBookmarkLast	2	Begins the search at the last record in the Recordset

Generally speaking, you'll want to use this parameter if you have a bookmark value other than the current record at which you want to begin the search, or if you want the search to begin with the first or last record in the Recordset.

One final note on the Start parameter. I was surprised to see that the adBookmarkFirst and adBookmarkLast values in this enumeration are positive integers. Why? In the discussion of the Bookmark property on the Recordset, I pointed out that client-side Recordsets use record numbers to denote their position. So how does the Find method determine the difference between adBookmarkLast and the bookmark for the second record in a client-side Recordset? Variants. The value of the Bookmark property for the second record in a client-side Recordset is 2, but it's stored as a double (don't ask me why) in a Variant. The Find method determines the data type of the data passed into this parameter to determine whether you're handing it a value from a Bookmark property or a value from BookmarkEnum. I don't know what made me decide to investigate this, but I found it interesting and thought I'd pass it along. A tip of the hat to whoever wrote that ADO code.

The Find method was a welcome addition to ADO (it was added in ADO 2.0) and contains much of the same functionality of the DAO Recordset's Find methods. ADO's search functionality, however, is not as robust as DAO's. Perhaps it will be more comparable in future versions. But remember that DAO does more than submit queries and handle the results. Unlike ADO, DAO is also a database engine that generates the results of Jet queries, which means it must have some fairly advanced searching routines.

ADO will probably never have the advanced search features of a database system such as SQL Server, Access, or Oracle, but that's not one of its primary purposes. If you really need advanced search capability, write your own code to walk through the contents of the Recordset to locate the desired record. If you even start to complain that ADO isn't able to handle your search with the Find method, imagine the code it takes to parse, analyze, and implement complex search criteria.

GetRows Method

Here's a helpful method that gets a lot less attention than its RDO and DAO counterparts did. GetRows returns a two-dimensional Variant array that contains data from your Recordset. Because neither DAO nor RDO could pass their objects out of process, this method was used a great deal in multitiered applications. Plus, by storing the results of your queries in Variant arrays, you had complete control over how you handled your data—for example, when you queried your database and how you updated your data (through action queries or stored procedures).

This approach is still perfectly valid and extremely powerful in ADO. If you use precious little of ADO's functionality, your chances of running into any compatibility problems from one version of ADO to the next are almost negligible. However, storing your data in Variants this way isn't sexy. It doesn't make use of functionality provided in rapid application development (RAD) tools such as ADO, and it doesn't sell products because there's little chance you'll use new features. The major drawback to this approach is that you have to write all your code yourself.

Back to the GetRows method. This method takes three parameters:

• Rows This parameter accepts a GetRowsOptionEnum data type constant and is optional. It specifies how many rows ADO will place in the Variant array returned by the GetRows method.
• Start This parameter accepts a string or a Variant data type and is optional. It accepts a bookmark value to control where ADO will begin reading records for the resulting Variant array.
• Fields This optional parameter accepts a Variant data type. It accepts a Variant array specifying names of the fields in the Recordset to place in the resulting Variant array.

The Rows parameter determines how many records ADO will place in your Variant array. This parameter takes a GetRowsOptionEnum value and defaults to adGetRowsRest (-1), which happens to be the only constant in the data type.

The second parameter, Start, accepts a string or a Variant to control where ADO should start placing data into your array. You can pass a bookmark value, adBookmarkFirst, or adBookmarkLast, just as you can with the Start parameter on the Find method.

You can use the third parameter, Fields, to control which fields appear in the two-dimensional Variant array that is returned from this method. The Fields parameter is optional and accepts a Variant array of field names, similar to the FieldList parameter of the AddNew method.

The GetRows method on the Recordset object does not require that the Recordset support bookmarks, unless you use the Start parameter. If you specify a value for the Start parameter that you retrieved from the Recordset's Bookmark property, the Recordset must support bookmarks (static or keyset cursor). If you specify adBookmarkFirst or adBookmarkLast, the Recordset needs to support only scrolling (static, keyset, or dynamic cursor).

After you've called the GetRows method, the current record in the Recordset is the first record not used in the Variant array. If there are no more records in the Recordset, EOF will be set to True.

GetString Method

The GetString method is similar to the GetRows method except that it builds a string rather than a Variant array. Although this method has five parameters (all optional), it's not as complex as you might think.

• StringFormat This parameter takes its value from StringFormatEnum. It specifies the format used to generate the string.
• NumRows This parameter accepts a long data type. It specifies how many records will be placed in the resulting string.
• ColumnDelimiter This parameter accepts a string data type. It specifies the value that ADO will use to delimit the columns in the resulting string.
• RowDelimiter This parameter accepts a string data type. It specifies the value that ADO will use to delimit the records in the resulting string.
• NullExpr This parameter accepts a string data type. It specifies how ADO will represent a Null value in the resulting string.

The first parameter, StringFormat, defaults to adClipString (2). In fact, this is the only available value for the parameter. Originally, the plan was to add functionality to build HTML table strings by means of this parameter, but the more thought the development team gave to this feature, the less appealing it became. They felt that only a small percentage of users would want to use a base HTML table with no built-in formatting or font features. If you want to build a relatively simple HTML table, you can use the ColumnDelimiter and RowDelimiter parameters. For a more complex HTML table—"I want negative balances to appear red, bold, and italic"—you'll need to loop through the Recordset and build the string by hand.

Use the second parameter, NumRows, to control how many records are used to build your string. This parameter accepts a long value and defaults to adGetRowsRest (-1).

The third and fourth parameters on the GetString method, ColumnDelimiter and RowDelimiter, control how the fields and records are delimited in your string. By default, these parameters are set to the tab and carriage return, respectively. These defaults are handy if you want to display the contents of your Recordset in one of the Visual Basic FlexGrids or in a Microsoft Excel spreadsheet. As just mentioned, you could build an HTML table by using the HTML table delimiters in these parameters.

The fifth and final parameter on this method is NullExpr. Null values don't translate easily to strings, and many programmers forget this when trying to display the contents of fields on a form. Null values in your Recordset are converted to the value in the NullExpr parameter, which defaults to an empty string.

Move Method

The Move method moves the cursor from one record to another within a Recordset. This method uses two parameters:

• NumRecords This parameter accepts a long data type. It specifies the number of positions that ADO will move the current record pointer.
• Start This optional parameter accepts a string or a Variant data type that contains a bookmark to control where the navigation will commence.

The first parameter, NumRecords, is required and accepts a long value to determine how many records to move from the current position. If you specify a negative value for this first parameter, ADO will move backward that number of records. Note, however, that you can't specify a negative value if the cursor is forward-only, unless you remain inside the currently cached records.

If you specify a number that would move the Recordset beyond its boundaries, the Recordset will be set to BOF or EOF, depending on the direction you're moving. It's worth mentioning that Move 0 in ADO does not behave the same way as it does in RDO. In fact, calling Move 0 in ADO does nothing whatsoever.

The Move method also accepts an optional parameter, Start, which controls where ADO will begin to move from. You can use this parameter only if your Recordset supports bookmarks (static or keyset cursor). This property defaults to adBookmarkCurrent, and you can set it to adBookmarkFirst, adBookmarkLast, or any valid bookmark value.

MoveFirst, MoveLast, MoveNext, and MovePrevious Methods

The MoveFirst method moves to the first record. MoveLast moves to the last record. MoveNext moves forward to the next record in the Recordset, and MovePrevious moves back one record. If you're using a Recordset that supports only scrolling forward, calling MoveFirst, MoveLast, or MovePrevious will generate a run-time error.

If you call the MoveNext method while you're currently examining the last record in your Recordset, the EOF property will be set to True. Trying to check the value of any of the fields in your Recordset while at EOF will generate a run-time error. Similarly, if you call MovePrevious while examining the first record in your Recordset, the BOF property will be set to True.

The following code uses the MoveNext method to move through the Recordset and print the contents of the CustomerID field for all records:

 strSQL = "SELECT * FROM Customers" rsCustomers.Open strSQL, cnDatabase, adOpenForwardOnly Do While Not rsCustomers.EOF Debug.Print rsCustomers.Fields("CustomerID").Value rsCustomers.MoveNext Loop 

If you have changes to a record pending (you've modified the current record but haven't yet called the Update or CancelUpdate method), ADO implicitly calls the Update method to commit those changes when you call one of these Move methods.

NextRecordset Method

Some database systems (such as Oracle and SQL Server) support batch queries, which allow you to submit multiple query strings at once. You can then process the results, one set at a time. In ADO, the NextRecordset method allows you to move from one set of results to the next set.

The NextRecordset method takes one parameter:

• RecordsAffected This output parameter will contain the number of records affected if the query modifies data in your database. It is a long data type and is optional.

If you wanted to execute a number of action queries and retrieve the number of records affected by each query, you could use code such as the following:

 strSQL = "DELETE FROM [Order Details] " & _ "WHERE OrderID = 10251; " & _ "DELETE FROM Orders WHERE OrderID = 10251" Set rsData = cnDatabase.Execute(strSQL, _ lngRecordsAffected) MsgBox lngRecordsAffected & " Order Detail(s) deleted." Set rsData = rsData.NextRecordset(lngRecordsAffected) MsgBox lngRecordsAffected & " Order(s) deleted." 

Because these are action queries, the only data returned is the number of records affected by the query; no records or fields are returned. If you were to check the State property of the Recordset object, you'd see a value of adStateClosed, so don't stop retrieving Recordsets when you receive one with this State value. Test until the returning Recordset is set to Nothing. Just make sure that you don't dimension the Recordset in Visual Basic using the keyword New. With this keyword, every time you check the object variable it will be set to a new Recordset object if the Recordset was set to Nothing. For example, look at the following code. The Do While loop in this code snippet is an infinite loop.

 Dim rsData As ADODB.Recordset strSQL = "SELECT * FROM Customers; " & _ "DELETE FROM Customers WHERE 1 = 0; " & _ "SELECT * FROM Orders" Set rsData = New ADODB.Recordset rsData.Open strSQL, cnDatabase, adOpenForwardOnly Do While Not (rsData Is Nothing) Set rsData = rsData.NextRecordset Loop 

In each of the code snippets we've used to demonstrate this method, we've stored the Recordset object returned from the call to NextRecordset in the same object variable we used to call NextRecordset. However, using the same object variable is not mandatory; you could return the next Recordset into a different one:

 strSQL = "SELECT * FROM Customers; " & _ "SELECT * FROM Orders" rsCustomers.Open strSQL, cnDatabase, adOpenStatic Set rsOrders = rsCustomers.NextRecordset 

If you're using a server-side Recordset, the data for the initial Recordset is lost as soon as you call NextRecordset. However, if you're using client-side Recordsets, you can scroll through the contents of each of these Recordsets. In the previous code example, the data in the rsCustomers Recordset is still available after the NextRecordset method is called. You also cannot pass the initial Recordset out of process and then call the NextRecordset method in the other process. This functionality might be available in a subsequent release.

ADO does not parse the query string that you pass to it. It simply passes this query string along to the OLE DB provider. It's up to the OLE DB provider and/or database system to parse and process the query or queries. ADO doesn't actually submit each query in a batch query separately, but it could in a future release.

The NextRecordset method is not available on client-side Recordsets when using Remote Data Service (RDS).

It's worth noting that action queries in a batch process or a stored procedure might return a closed, empty Recordset. SQL Server users can prevent retrieving unwanted Recordsets by using SET NOCOUNT ON and SET NOCOUNT OFF to control this behavior. Check your SQL Server documentation for more information on this feature.

Open Method

The Open method is the most powerful and versatile method of retrieving data from your database. You can set the ActiveConnection, Source, LockType, and CursorType properties on the Recordset prior to using the Open method, or you can supply this data in its parameters, all of which are optional:

• Source This parameter accepts a Variant. You can use the Source parameter to specify the query string or Command object you want to use. If you do use a Command object in this parameter, be sure to leave the ActiveConnection parameter on this method blank. Instead, set the ActiveConnection property on the Command object. This parameter can also contain a table name, a stored procedure call, a URL, a filename, or a Stream object.
• ActiveConnection This parameter accepts a Variant in the form of a connection string or an open Connection object, just like the ActiveConnection property.
• CursorType This parameter accepts a CursorTypeEnum value. (See "CursorType Property" for a list of CursorTypeEnum values.)
• LockTypeThis parameter accepts a value from LockTypeEnum. (See "LockType Property" for a list of LockTypeEnum values.)
• Options This parameter accepts a CommandTypeEnum value and/or a combination of asynchronous ExecuteOptionEnum constants, as respectively shown in the following two tables.

CommandTypeEnum Values
Constant	Value	Description
adCmdText	1	ADO will pass the query string as is to the OLE DB provider.
adCmdTable	2	ADO will assume that the query string is the name of a table and attempt to modify the query string appropriately.
adCmdStoredProc	4	ADO will assume that the query string is the name of a stored procedure and attempt to modify the query string appropriately.
adCmdUnknown	8	ADO will try to execute the query string in a variety of ways.
adCmdFile	256	ADO will open the Recordset from a file.
adCmdTableDirect	512	ADO will attempt to use an optional interface on OLE DB providers to retrieve the contents of the table.

ExecuteOptionEnum Values
Constant	Value	Description
adAsyncExecute	16	ADO will execute the query string asynchronously.
adAsyncFetch	32	ADO will fetch the results of the query asynchronously.
adAsyncFetchNonBlocking	64	ADO will fetch the results of the query asynchronously without blocking the main thread.
adExecuteNoRecords	128	No records are returned from the query. (This option isn't available on the Recordset object.)

We've discussed most of these parameters earlier in this chapter in terms of their use as properties. We'll also talk more about cursors and updating your database in later chapters. For now, let's talk about the Options parameter and using values from CommandTypeEnum and ExecuteOptionEnum. The Options parameter is a bitmask, so you can use the sum of constants, such as adCmdTable + adAsyncExecute, as shown in the following code:

 rsCustomers.Open "Customers", cnNorthwind, _ adOpenStatic, adLockReadOnly, _ adCmdTable + adAsyncExecute 

In an attempt to make ADO friendlier, the concept of a CommandType was introduced. You can specify a table name or a stored procedure name, and ADO will try to execute that query string on its own, as well as trying to retrieve the contents of a table with the same name or execute a stored procedure of the same name. While this functionality is helpful for the novice developer, it means that ADO might need to pass multiple query strings to your OLE DB provider before it finds a query string that executes successfully.

Although I appreciate this attempt to simplify things, I would never ask ADO to figure out what type of query I'm executing unless absolutely necessary. Besides, it's not always as simple as it sounds. When you pass ADO a table name and specify adCmdTable in the Options parameter, ADO simply prepends "select * from " to the query string. If the table you want to query contains a space in its name, this query will fail if you did not delimit the table name yourself. This point is illustrated by the following code:

 'This will fail. rsOrderDetails.Open "Order Details", cnNorthwind, _ adOpenStatic, adLockReadOnly, _ adCmdTable 'This will succeed. rsOrderDetails.Open "[Order Details]", cnNorthwind, _ adOpenStatic, adLockReadOnly, _ adCmdTable 

Don't get me wrong—there are times when I use this functionality; I just grin (or grouse) and bear it when it doesn't work the way I'm expecting it to. I suggest using the adCmdText constant for your CommandType, but I'll admit I do use the adCmdFile constant when I want to reopen a Recordset that I had previously persisted to a file:

 rsCustomers.Open "C:\Customers.rst", Nothing, _ adOpenStatic, adLockReadOnly, adCmdFile 

We'll talk about this feature more in Chapter 13, when we discuss persistence.

ADO 2.5 allows you to use a Stream object that contains Recordset data (stored by using the Recordset's Save method) in the Source parameter.

The other constant that I use from time to time in the Options parameter is adCmdTableDirect. This command type closely resembles DAO's table-type Recordset. Some OLE DB providers will implement an optional feature that can provide better performance when retrieving all records and columns from a table. If you need to retrieve the entire contents of a particular table, you might want to try using this constant and find out whether your OLE DB provider supports it and whether it will improve the performance of your application.

You can also use the Options parameter on the Recordset.Open method to utilize ADO's asynchronous functionality. If you specify adAsyncExecute, the query will be executed asynchronously.

The adAsyncFetch and adAsyncFetchNonBlocking options apply to client-side Recordsets only. Once ADO has retrieved an initial number of records, the rest of the records are fetched on a background thread. If you attempt to move to a record that has not been fetched and you use the adAsyncFetch constant, the main thread will be blocked while ADO continues to retrieve data. Once that record is available, the main thread will resume. With the adAsyncFetchNonBlocking constant, if you move beyond the records that have been fetched, you'll move to EOF instead and the main thread will not be blocked.

Requery Method

The Requery method on the Recordset object reexecutes the query that created the Recordset object. It has the following parameter:

• Options This optional parameter accepts an ExecuteOptionEnum value. (See the table in "Open Method" for a list of ExecuteOptionEnum values.) You can call the Requery method asynchronously by setting this parameter to adAsyncExecute.

You can use this method on server-side or client-side Recordsets. This method is very useful if you want to repeatedly execute the same parameterized query. Simply change the value of the appropriate Parameter object or objects, and then call the Requery method on the Recordset object. We'll talk more about parameterized queries in Chapter 5, when we discuss the Command object.

When you call the Requery method, you're rerunning the query—similar to calling the Close and then Open methods on the Recordset object. Although your data will be fresher than it was before you called the Requery method, the contents of your Recordset might have changed dramatically. Changes made by other users (modifications, insertions, and deletions) will affect your query. Records that were in your Recordset prior to calling Requery might not appear in the Recordset after the call. For these reasons, you should not rely on bookmark values retrieved prior to calling Requery. In many cases, using a previously retrieved bookmark for a server-side Recordset will result in a run-time error.

Resync Method

The Resync method is similar to the Requery method: you use it to refresh the data in your Recordset. It has the following two parameters:

• AffectRecords An optional parameter, it accepts a value from AffectEnum. Use this parameter to control which records you want to resynchronize. This parameter defaults to adAffectAll.
• ResyncValues This parameter is optional and accepts a value from ResyncEnum. Use this parameter to control where ADO stores the newly retrieved data. This parameter defaults to adResyncAllValues.

The Resync method differs from the Requery method in that it does not reexecute the query that generated the Recordset object. Instead, ADO examines the record you want to refresh and executes a separate query to retrieve the current contents of that record in the database. For example, if you created your Recordset with the query

 SELECT CustomerID, CompanyName, BalanceDue FROM Customers 

calling the Resync method would cause ADO to execute the following query for each record, where x is the value of the CustomerID for each record in the Recordset:

 SELECT CustomerID, CompanyName, BalanceDue FROM Customers WHERE CustomerID = x 

Since you're not actually reexecuting the query that you originally submitted to generate the data in your Recordset, new records that might satisfy the results of your query will not be retrieved when calling the Resync method.

This method is not available for server-side Recordsets. If you call the Resync method on a server-side Recordset, you'll generate a run-time error. Prior to ADO 2.0, calling Resync on a server-side Recordset did not generate an error; ADO executed the Requery method instead.

You can use the Resync method only on client-side Recordset objects that are not marked read-only. If the Recordset is read-only, the ADO Cursor Engine does not need to retrieve much metadata for your Recordset, such as which fields constitute the primary key or which fields are updatable. Without this metadata, ADO cannot create the queries to retrieve the most up-to-date data for the records in your Recordset.

You can use the first parameter, AffectRecords, to control which records you want to resynchronize. By default, this parameter is set to adAffectAll. For more information on the behavior associated with the different possible values for the AffectRecords parameter, see the discussion about the AffectEnum enumeration in the CancelBatch Method section.

The second parameter, ResyncValues, is designed to help manage batch updates that fail as a result of modifications made by other users. By default, ADO will store the newly retrieved data in the Value property of the Field objects in your Recordset. If you're attempting to determine why some batch updates failed, you probably don't want to overwrite the information you currently have stored in the Value property. When you specify adResyncUnderlyingValues in the optional ResyncValues parameter, ADO will store the newly retrieved data in the UnderlyingValue property of the Field objects instead of in the Value property. We'll discuss these properties in more depth as we cover how to handle optimistic updating conflicts in Chapter 12.

Save Method

You can call the Save method to save the contents of a Recordset to a file and reopen it later. This method accepts two parameters:

• Destination This is an optional parameter. It accepts a string containing the filename where you want to save the recordset or a Stream object.
• PersistFormat This optional parameter accepts a value from PersistFormatEnum; it controls the format for the recordset file. PersistFormat defaults to adPersistADTG but can be set to adPersistXML as of ADO 2.1.

The first time you save a recordset, you must specify a filename in the Destination parameter. Only the records currently visible will be stored in the file. If you specify a filename that already exists, you'll generate a run-time error. Once you've opened your recordset from a file, you can resave its contents to that file and overwrite the old data by omitting the Destination parameter.

The Save method was added in ADO 2.0, and the only available format was adPersistADTG—the same format used when passing a Recordset object out of process. As of ADO 2.1, you can also save a Recordset to an XML file through the adPersistXML constant. Although the ability to write to an XML file is part of ADO 2.1 and later, the ability to turn an XML file back into a Recordset is actually implemented through the Microsoft Internet Explorer XML parser. If you do not have the parser that is included with Internet Explorer 5 on your machine, you will not be able to open the Recordset you saved into an XML file.

Seek Method

At the time of this writing, the only OLE DB provider that supports the Seek method is the Jet 4.0 OLE DB Provider, but only when used with Jet 4 and Access-formatted databases and server-side Recordsets with a CommandType of adCmdTableDirect. This method is similar to the Find method, but the OLE DB provider, rather than the ADO Cursor Engine, does the searching. To determine whether your OLE DB provider supports this functionality, use the Supports method on the Recordset object with the adSeek constant. The Seek method has two parameters:

• KeyValues This parameter is required. It accepts a Variant array of the key values—one value per field in the current index. If the index in use is based on a single field, you can omit the Variant array and simply supply the key value you want to locate.
• SeekOption This parameter is optional and accepts one of the values from SeekEnum shown in the following table. It defaults to adSeekFirstEQ.

SeekEnum Values
Constant	Value	Description
adSeekFirstEQ	1	Seeks the first key matching the criteria
adSeekLastEQ	2	Seeks the last key matching the criteria
adSeekAfterEQ	4	Seeks the first key that matches the criteria; if none are found, seeks the key following the position where the key would be found
adSeekAfter	8	Seeks the key following the key in the criteria
adSeekBeforeEQ	16	Seeks the first key that matches the criteria; if none are found, seeks the key prior to the position where the key would be found
adSeekBefore	32	Seeks the key prior to the key in the criteria

The code that follows is an example of how to use the Seek method with an Access 2000 database:

 Set rsCustomers = New ADODB.Recordset rsCustomers.Open "Customers", cnNorthwind, adOpenStatic, _ adLockReadOnly, adCmdTableDirect rsCustomers.Index = "City" rsCustomers.MoveFirst rsCustomers.Seek Array("London") 

Supports Method

You can use the Supports method on the Recordset object to understand what functionality is available based on your choice of OLE DB provider, CursorLocation, CursorType, and LockType. It takes one parameter:

• CursorOptions This parameter is required and accepts one of the values from CursorOptionEnum, shown in this next table.

CursorOptionEnum Values
Constant	Value	Functionality Your Recordset Supports
adAddNew	16778240	AddNew method
adApproxPosition	16384	AbsolutePage and AbsolutePosition properties
adBookmark	8192	Bookmark property
adDelete	16779264	Delete method
adFind	524288	Find method
adHoldRecords	256	Very similar to bookmarks
adIndex	8388608	Index property
adMovePrevious	512	MovePrevious, MoveFirst, Move, and GetRows methods
adNotify	262144	Events
adResync	131072	Resync method
adSeek	4194304	Seek method
adUpdate	16809984	Update method
adUpdateBatch	65536	UpdateBatch and CancelBatch methods

If you're building an application that allows the user to generate ad hoc queries, you might want to use the Supports method to determine which functionality is available in the Recordset you've just opened.

To be truthful, I don't know the purpose of adHoldRecords. The documentation implies that you can navigate through the Recordset and retrieve more data without having to commit pending changes, but the function returns True on read-only Recordsets when using this constant.

Update Method

Use the Update method to commit changes to the current record. When you call the Update method, the changes are submitted to the database as well as to the Recordset unless you're using batch updates (LockType = adLockBatchOptimistic). In that case, the changes in the modified records are cached until you call the UpdateBatch method. The Update method has two optional parameters:

• Fields This parameter accepts a Variant array of field names that you want to update.
• Values This parameter accepts a Variant array of values that correspond to the fields you want to update.

You can use the Update method in one of two ways. You can modify the Value property of Field objects and then call the Update method, or you can call the Update method and use the optional parameters as shown:

 rsCustomers.Update Array("CompanyName", "BalanceDue"), _ Array("New Name", 100.00) 

This code is equivalent to:

 rsCustomers.Fields("CompanyName").Value = "New Name" rsCustomers.Fields("BalanceDue").Value = 100.00 rsCustomers.Update 

Note that there is no Edit method on the Recordset object. If you want to modify a record, you can start by modifying the Value property on the desired Field objects, or you can use the optional parameters on the Update method as was shown.

If you call the Update method on a client-side Recordset that is not using batch updates and does not have a connection to the database, ADO will not generate a run-time error and will mark that record as if it had successfully updated a database.

UpdateBatch Method

If you're using batch updates, call UpdateBatch to submit the cached changes in the Recordset to your database. This method has one parameter:

• AffectRecords This is an optional parameter that accepts a value from AffectEnum. It defaults to adAffectAll and determines which records' pending changes you want to submit to the database.

You can use the AffectRecords parameter to control which records' changes will be submitted to the database. (See the "CancelBatch Method" section in this chapter for more information on the behavior associated with each of the AffectEnum values.)

If you call the UpdateBatch method on a client-side Recordset that doesn't have a connection to the database, ADO will not generate a run-time error and will mark the applicable records as if they were successfully updated in a database.