The Recordset object

A recordset is a programmatic construct for working with records. You can base your records on a table or a view in the current project or on another file, a SQL statement, or a command that returns rows. What you can do with a recordset depends on its OLE DB provider and on native data source attributes.

While you can extract recordsets using other objects, such as connections and commands, the Recordset object's rich mix of properties and methods make it a natural choice for doing much of your row-set processing. You can use recordsets to perform multiple actions against a set of rows: You can navigate between rows; print all or some of their contents; add, revise, and delete records; find records; and filter records to select one or any subset from a full recordset. Recordsets have historically been nonpersistent objects-they normally exist just for the time that they are open in a program. The 2.10 version of ADO that ships with Access 2000 offers persistent recordsets, which you can save to disk and then open again later.

The ActiveConnection property A recordset's ActiveConnection property lets your application tap an open connection to support a recordset. You can set this property any time after setting the object for the recordset. Its use simplifies your Open method statement for the recordset by removing the need to include the connection information. When you preset the property, you do not even need to reference an existing connection in the Open method statement.

The Open method The recordset's Open method is one common route for making a recordset available in a procedure. The source argument is the most critical one for this method. It designates the data source on which the method patterns the object that it opens. Typical options for the source argument include a table, a SQL statement, a saved recordset file, or a stored procedure. You use the Open method's Options argument to designate the source type when you open a recordset.

The cursor type The cursor type is among the most basic features of a recordset. It determines how you can navigate through the recordset and the types of locks that you can impose on it. ADO supports four cursor types:

· Dynamic This type of cursor lets users view changes to a data source made by other users. It enables recordset maintenance functions such as adding, changing, and deleting records, and it permits bidirectional navigation around a database without relying on bookmarks.

Keyset This cursor has most of the properties of a dynamic cursor, except you do not have ready access to changes by other users of a data source. One way to view changes made by others is to invoke a recordset's Requery method.
Static This cursor is a snapshot of a recordset at a point in time. It allows bidirectional navigation. Changes to the database by other users are not visible. Microsoft Internet Explorer 4 and later supports this type as its most flexible client-side cursor.
Forward-only Sometimes called the fire hydrant cursor, this type goes in one direction and can speed up cursor performance. This is the default ADO cursor type. If you need another type of cursor, you must set the CursorType property before opening the recordset.

NOTE

The cursor type setting interacts with lock type settings. If you designate a forward-only cursor type with a lock type other than read-only (adLockReadOnly), ADO overrides your cursor type setting. For example, ADO automatically converts a forward-only cursor type to a keyset cursor type if you designate optimistic locking.

The LockType property The LockType property partially interacts with the cursor type because it controls how users can manipulate a recordset. One lock type setting (adLockReadOnly) specifically matches forward-only cursors. This is the default lock type. The following table describes the four possible settings for the LockType property. The adLockBatchOptimistic setting is specifically for remote databases, such as SQL Server or Oracle, as opposed to a local Jet database. This topic will receive more attention in Chapter 12.

Constants Used to Set the Connection Object's LockType Property

*Constant*	*Value*	*Behavior*
adLockReadOnly	1	Read-only access (default)
adLockPessimistic	2	Locks a record as soon as a user chooses to start editing it
adLockOptimistic	3	Locks a record only when a user chooses to commit edits back to the database
adLockBatchOptimistic	4	Allows edits to a batch of records before an attempt to update a remote database from the local batch of records

NOTE

You can determine whether the recordset you are using provides a particular type of functionality by using the Supports method. You simply put the constant that represents that functionality in parentheses when you invoke Supports. A return value of True indicates that the recordset provides that functionality. The online Supports documentation describes the names of the constants. Search the Object Browser for CursorOptionEnum to see a list of constants for which Supports returns True or False.

Recordset navigation Four methods enable recordset navigation by changing the current record position:

· MoveFirst This method changes the current record position to the first record in a recordset. The order of records depends on the current index, or, if there is no index, on the order of entry. This method functions with all cursor types. Its use with forward-only cursors can force a reexecution of the command that generated the recordset.

MoveLast This method establishes the last record in a recordset as the current record position. It requires a cursor type that supports backward movement or at least movement based on bookmarks. Using the method with a forward-only cursor generates a run-time error.
MoveNext This method relocates the current record position one record forward (in the direction of the recordset's final record). If the current record position is the last record, the recordset's EOF property is set to True. If this method is called when the recordset's EOF property is already True, a run-time error results.
MovePrevious This method sends the current record position one record backward. If the current record position is the first record, the recordset's BOF property is set to True. If this method is called when the recordset's BOF property is already True, a run-time error results. This method also generates a run-time error if you use it with a forward-only cursor type.

The Move method works differently than the other four recordset navigation methods because it can move the current record position a variable number of records in either direction. You use a positive argument to indicate moves toward the last record and a negative argument to identify moves toward the first record. If a move will extend beyond the first or last record, the Move method sets the recordset's BOF or EOF property to True. If that property is already True, the Move method generates a run-time error. Movement is relative to the current record unless you specify a Start parameter that can enable movement from the first or last record.

You can enhance the Move method's performance in a couple of ways by using it with a recordset's CacheSize property set to greater than the default value, which is 1. CacheSize settings cause ADO to store a fixed number of records in the local workstation's memory. Since it is much faster to retrieve records from memory than from a provider's data store, you can speed record navigation with Move by using a larger CacheSize. With a forward-only cursor and a larger CacheSize, you can actually enable backward as well as forward scrolling. If your cache setting is equal to the number of records in a recordset, you can scroll the full extent of the recordset in both directions. The CacheSize property does not enable backward scrolling with the MovePrevious method. (You can use the Move method with a negative argument.)

The Find method The recordset's Find method searches for the first record that matches a specified selection criterion. While this method bears a striking similarity to a collection of Find methods in earlier versions of Access, the Access 2000 version has a different syntax and behavior. Rather than attempt to map the similarities and differences, you should simply learn the syntax and behavior of the new version.

The new Find method takes as many as four arguments. The first argument is required and is the criterion for the search. Its syntax follows that of SQL statement WHERE clauses. If you do not specify any other arguments, the method searches from the current record through the last record to find a record that matches the criterion. Once the method finds a match, you must move off that record to find a new match in the recordset. If there is no match, the method sets the recordset's EOF property to True. See the online help for a description of the remaining three optional arguments.

The Sort property A recordset Sort property can affect the results of both the Find and Move methods. This property designates one or more fields that can determine the order in which rows display. The Sort property setting allows the designation of an ascending or descending order for any field. The default is ascending order. The Sort property settings do not physically rearrange the rows-they merely determine the order in which a recordset makes its rows available.

The Filtered property The Filtered property for a recordset defines a new recordset that is a filtered version of the original recordset.While this property has specialized applications for database synchronization and batch updating a remote data source, it can also be a simple alternative to defining a new recordset based on a SQL statement. If you already have a recordset and you need only a subset for another purpose, this property can serve admirably.

The AddNew method The AddNew method adds new records to a recordset. After you invoke the method, you set the values for the fields in a new row that you want to add. Then you either move off the record using a Move method or you call the Update method while still on the row. (You can modify the values in a field using a similar pair of techniques. You update fields by assigning them new values, and then you move off the record. Alternatively, you can remain on an edited record as long as you call the Update method. You can delete a record by simply navigating to it and then calling the Delete method. The deleted record remains current until you move away from it.)

Printing field values The following simple procedure opens a data source and then successively prints out the rows of the database. A loop passes through all the records and prints the first two fields of each record.

Sub EasyLoop()

Dim rsCustomers As Recordset

    Set rsCustomers = New ADODB.Recordset

    rsCustomers.Open "customers", & _

        "Provider=Microsoft.Jet.OLEDB.4.0;" & _

        "Data Source=C:\Program Files\Microsoft Office\Office\" & _

        "Samples\Northwind.mdb;"

'Loop through recordset.

    Do Until rsCustomers.EOF

        Debug.Print rsCustomers.Fields(0), rsCustomers.Fields(1)

        rsCustomers.MoveNext

    Loop

    rsCustomers.Close

End Sub

One weakness of the first EasyLoop procedure is that it prints only the values of the fields you specifically request. The EasyLoop2 procedure below circumvents this difficulty. No matter how many fields are in the data source for a recordset, the procedure automatically prints all of them.

Sub EasyLoop2()

Dim rsCustomers As Recordset

Dim fldMyField As Field

Dim strForRow As String

    Set rsCustomers = New ADODB.Recordset

    rsCustomers.Open "customers", & _

        "Provider=Microsoft.Jet.OLEDB.4.0;" & _

        "Data Source=C:\Program Files\Microsoft Office\Office\" & _

        "Samples\Northwind.mdb;"

'Loop through recordset and fields with rows.

    Do Until rsCustomers.EOF

        strForRow = ""

        For Each fldMyField In rsCustomers.Fields

            strForRow = strForRow & fldMyField & "; "

        Next fldMyField

        Debug.Print strForRow

        rsCustomers.MoveNext

    Loop

    rsCustomers.Close

End Sub

The first several and last several lines in each procedure are identical. The EasyLoop2 procedure nests a For loop inside a Do loop. This inner For loop enumerates the fields in a row and builds a string with all the field values on each row. (The string is cleared at the top of the loop to start the process over again for another row.)

Looping is an easy way to perform an operation on the rows and columns within a recordset. However, it is not the most efficient way to retrieve the field values of a recordset. The NoEasyLoop procedure below uses the GetString method to retrieve and print all the fields on all rows of a recordset in one step. The GetString method returns a recordset as a string. It can take up to five arguments; the code uses three of those arguments. You designate the adClipString constant as the first argument-this is your only choice. It specifies the format for representing the recordset as a string. The second argument specifies the number of recordset rows to return. This code returns five rows. Leaving this argument blank enables the method to return all the rows in the recordset. The third argument designates a semicolon delimiter for the columns within a row. The default column delimiter is a tab. The fourth and fifth arguments, neither of which appears below, specify a column delimiter and an expression to represent null values. The default values for these arguments are a carriage return and a zero-length string.

Sub NoEasyLoop()

Dim rsCustomers As Recordset

    Set rsCustomers = New ADODB.Recordset

    rsCustomers.Open "customers", _

        "Provider=Microsoft.Jet.OLEDB.4.0;" & _

        "Data Source=C:\Program Files\Microsoft Office\Office\" & _

        "Samples\Northwind.mdb;"

'Print records without a loop.

    Debug.Print rsCustomers.GetString(adClipString, 5, "; ")

    rsCustomers.Close

End Sub

The GetString method replaces a pair of nested loops. If the defaults are acceptable, you can use the method without any arguments. This makes for a simple way to extract values from a recordset. Although nested loops are the intuitive way to retrieve values from a recordset, the GetString method can achieve a similar result in a single line.

Adding a record The following code tackles a new task-adding a new record to a data source.

Sub AddARecord()

Dim rsMyTable As Recordset

'Set your cursor so that it is not read-only to delete.

    Set rsMyTable = New ADODB.Recordset

    rsMyTable.ActiveConnection = CurrentProject.Connection

    rsMyTable.Open "MyTable", , adOpenKeyset, adLockOptimistic, _

        adCmdTable

'Invoke the AddNew method.

    rsMyTable.AddNew

    rsMyTable.Fields("Column1").Value = 16

    rsMyTable.Fields("Column2").Value = 17

    rsMyTable.Fields("Column3").Value = 18

    rsMyTable.Update

End Sub

While EasyLoop, EasyLoop2, and NoEasyLoop all accept the Open method's default cursor type and lock type settings, the AddARecord procedure does not. Recall that the defaults are a forward-only cursor and a read-only lock type. These settings are acceptable for merely printing the contents of a recordset. However, you need a cursor and a lock type that permit updates to a recordset when your task requires adding, editing, or deleting records. The adOpenKeyset and adLockOptimistic arguments to Open allow you to add new rows to a recordset. Also, notice that the ActiveConnection setting in the code above does not reference the Northwind sample project. It instead references the connection for the current project. When you need to reference a data source in the current project, use this syntax. The connection statement also explicitly designates a table in the current project as the data source for the recordset. There are several alternative sources, including the text for a SQL statement, a stored procedure, an external file saved in a special format, and more.

To use the AddNew method to add a record, you call the method, issue assignment statements to populate the new record with values, and then invoke the Update method. The call to Update is not strictly mandatory; you can simply move off the new, current record. For example, you can invoke MoveFirst or another method to navigate to a new record.

Editing or deleting a record The following code edits or deletes a record. It does not use the Edit and Update methods to save the edited records. Instead, it moves off the record. If it is impractical to move off the record or if your application needs to commit the changes before moving, use the recordset's Update method instead.

Sub DeleteOrUpdateARecord()

Dim rsMyTable As Recordset

'Use a non-read-only lock type to be able to delete records.

    Set rsMyTable = New ADODB.Recordset

    rsMyTable.ActiveConnection = CurrentProject.Connection

    rsMyTable.Open "MyTable", , adOpenKeyset, adLockOptimistic, _

        adCmdTable

'Loop through recordset.

    Do Until rsMyTable.EOF

        If rsMyTable.Fields("Column1") = 16 Then

'            rsMyTable.Fields("Column1") = 88

            rsMyTable.Delete

        End If

        rsMyTable.MoveNext

    Loop

    rsMyTable.Close

End Sub

A loop such as the one in the DeleteOrUpdateARecord procedure can help you select records for deleting or editing. The procedure examines each Column1 field value in a recordset, searching for one with a value of 16. When it finds one, it deletes the row. Notice that the loop contains a comment line. To switch from a delete routine to an updating routine, you simply transfer the comment mark from the assignment line to the Delete method line.

Finding records Another common use for a recordset is to find one or more records that meet specified criteria. Access 2000 offers several approaches to this task. With earlier versions of Access, many developers used one or more variations of the Find method. As mentioned earlier, Access 2000 offers a single Find method that works somewhat differently from the earlier Find methods. If you liked the earlier Find methods, you can use the new Find method with its similar functionality.

The following code shows a simple application of the Find method that searches for a record with a customer ID that begins with the letter D. When it finds a record matching its criteria, the method relocates the current record to that location. The code prints the CustomerID and ContactName fields to confirm exactly which record matches the criteria.

Sub FindAMatch()

Dim rsCustomers As Recordset

    Set rsCustomers = New ADODB.Recordset

    rsCustomers.ActiveConnection = _

        "Provider=Microsoft.Jet.OLEDB.4.0;" & _

        "Data Source=C:\Program Files\Microsoft Office\Office\" & _

        "Samples\Northwind.mdb;"

    rsCustomers.Open "Customers", , adOpenKeyset, adLockPessimistic, _

        adCmdTable

    rsCustomers.Find ("CustomerID Like 'D*'")

    Debug.Print rsCustomers.Fields("CustomerID"), _

    rsCustomers.Fields("ContactName")

End Sub

One drawback to this approach is that it searches for a single match to the criteria, and then stops immediately after finding it. The code below discovers all the records that match the criteria statement. This simple application reveals more of the flexibility of the Find method.

Sub FindAMatch2()

Dim rsCustomers As Recordset

    Set rsCustomers = New ADODB.Recordset

    rsCustomers.ActiveConnection = & _

        "Provider=Microsoft.Jet.OLEDB.4.0;" & _

        "Data Source=C:\Program Files\Microsoft Office\Office\" & _

        "Samples\Northwind.mdb;"

    rsCustomers.Open "Customers", , adOpenKeyset, _adLockPessimistic,

        adCmdTable

Do

        rsCustomers.Find ("CustomerID Like 'D*'")

        If rsCustomers.EOF Then

            Exit Sub

        End If

        Debug.Print rsCustomers.Fields("CustomerID")

        rsCustomers.MoveNext

    Loop

End Sub

The trick to finding all the records that match the search criteria is to embed the Find method in a Do loop. When the Find method sets the recordset's EOF property to True, there are no additional matching records. In this case, the code executes an Exit Sub statement to end the subroutine. As long as Find keeps discovering new matches, the procedure prints the customer IDs in the Immediate window. After printing a matching record, the procedure advances the current record by one. Without this, the Find method would repeatedly return the same record.

The Find method goes through a recordset sequentially and discloses matches one at a time. It does not create another version of the recordset that contains all the records that match the criteria. When you need a new or alternate recordset containing just the matches, your application needs a different approach. The recordset Filter property might be the answer. This property lets you designate a simple criterion for a field, and it returns a filtered version of the original recordset with only those records that match the criterion. By setting the Filter property equal to any of a series of constants, you can achieve special effects for database replication or for updating a remote data source. One filter constant, adFilterNone, removes the filter setting from a recordset and restores the original values.

Filtering records The two following procedures filter a recordset based on the Customers table in the Northwind database. The FilterRecordset procedure manages the overall use of the Filter property, prints the result set, clears the filter, and then prints the result set again. The FilterRecordset procedure relies on the FilterLikeField function to manage the setting of the Filter property based on parameters passed to it by the FilterRecordset procedure.

Sub FilterRecordset()

Dim rsCustomers As Recordset

'Create recordset variable.

    Set rsCustomers = New ADODB.Recordset

    rsCustomers.ActiveConnection = & _

        "Provider=Microsoft.Jet.OLEDB.4.0;" & _

        "Data Source=C:\Program Files\Microsoft Office\Office\" & _

        "Samples\Northwind.mdb;"

'Open recordset.

    rsCustomers.Open "Customers", , , , adCmdTable

'Filter recordset.

    Set rsCustomers = _

        FilterLikeField(rsCustomers, "CustomerID", "D*")

    Debug.Print rsCustomers.GetString

'Restore recordset.

    rsCustomers.Filter = adFilterNone

    Debug.Print rsCustomers.GetString

    rsCustomers.Close

End Sub

Function FilterLikeField(rstTemp As ADODB.Recordset, _

    strField As String, strFilter As String) As ADODB.Recordset

'Set a filter on the specified Recordset object and then

'open a new Recordset object.

    rstTemp.Filter = strField & " LIKE '" & strFilter & "'"

    Set FilterLikeField = rstTemp

End Function

The FilterRecordset procedure starts by creating and opening the rsCustomers recordset. Next, it applies a filter by calling the FilterLikeField function, which takes three arguments and returns a filtered recordset based on them. FilterRecordset assigns the filtered return set to rsCustomers and prints it to confirm the result.

The arguments to FilterLikeField include rsCustomers, a field name on which to filter records, and a filter criterion value, which can include any legitimate expression for the Like operator used by FilterLikeField. FilterRecordset passes D* to find just the records that have a CustomerID beginning with the letter D. The Filter property does not restrict you to filtering with the Like operator. Other acceptable operators include <, >, <=, >=, <>, and =. You can also include And and Or operators in your criteria expressions to combine two or more criteria expressions based on the other legitimate operators.

The Filter property does restrict your criteria expressions to those of the form FieldName-Operator-Value. However, some Filter constants enable special uses of the property. The FilterRecordset procedure uses the adFilterNone property to restore a recordset by removing its filters.

Using SQL to create a recordset You should know one final thing about recordsets: how to generate recordsets based on SQL statements. SQL statements are often nothing more than "SELECT * FROM TABLENAME", but you can tap the full functionality of SQL to generate recordsets. You can even use complex multitable SELECT statements with computed fields that use either inner or outer joins and that constrain or organize return sets with WHERE, GROUP BY, and ORDER BY clauses. One easy way to create a custom recordset based on SQL statements is by using WHERE clauses. You can selectively extract records from an existing source using expressions that are more complicated than when you use the Filter property.

The following code uses an Open method with a SQL statement. When you base a recordset on a SQL statement instead of an existing table, you pass your SQL statement and use the optional adCmdTable argument instead of adCmdText. That's all there is to it. You can then use the recordset to construct any simpler recordset based on an individual table. More complicated SQL statements do not alter how you declare or use the recordset with ADO.

Sub SQLRecordset()

Dim rsCustomers As Recordset

'Create recordset variable.

    Set rsCustomers = New ADODB.Recordset

    rsCustomers.ActiveConnection = & _

        "Provider=Microsoft.Jet.OLEDB.4.0;" & _

        "Data Source=C:\Program Files\Microsoft Office\Office\" & _

        "Samples\Northwind.mdb;"

'Open the recordset.

    rsCustomers.Open "SELECT * FROM Customers", , adOpenForwardOnly, _

        adLockReadOnly, adCmdText

    Debug.Print rsCustomers.GetString

    rsCustomers.Close

End Sub