Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources]

Working with ADO Recordset Properties and Methods

The ADO Recordset object is rich with properties and methods. These properties and methods allow you to move through a recordset, sort, filter, and find data, as well as update data contained with the recordset. The sections that follow cover the most commonly used properties and methods.

Examining Record-Movement Methods

When you have a Recordset object variable set, you probably want to manipulate the data in the recordset. Table 14.4 shows several methods you can use to traverse through the records in a recordset.

Table 14.4. Methods for Moving Through the Records in a Recordset

Method	Moves
MoveFirst	To the first record in a recordset
MoveLast	To the last record in a recordset
MovePrevious	To the previous record in a recordset
MoveNext	To the next record in a recordset

Listing 14.14 shows an example of using the record-movement methods on a Recordset object.

Listing 14.14 Using the Recordset-Movement Methods on a Recordset Object

Sub RecordsetMovements() Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset 'Establish the connection and cursor type and open 'the recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.Open "Select * from tblProjects" 'Print the ProjectID of the first row Debug.Print rst("ProjectID") 'Move to the next row and print the ProjectID rst.MoveNext Debug.Print rst("ProjectID") 'Move to the last row and print the ProjectID rst.MoveLast Debug.Print rst("ProjectID") 'Move to the previous row and print the ProjectID rst.MovePrevious Debug.Print rst("ProjectID") 'Move to the first row and print the ProjectID rst.MoveFirst Debug.Print rst("ProjectID") rst.Close Set rst = Nothing End Sub

This code opens a recordset based on the tblProjects table. When the recordset is open, the ProjectID of the first record is printed to the Immediate window. The MoveNext method of the Recordset object is used to move to the next record in the recordset. The ProjectID of the record is printed. The MoveLast method of the Recordset object is used to move to the last record in the recordset. Once again, the ProjectID is printed. The MovePrevious method moves the record pointer back one record and the ProjectID is printed again. Finally, the MoveFirst method moves the record pointer to the first record and the ProjectID is printed. The code closes the recordset and destroys the Recordset object.

Detecting the Limits of a Recordset

Before you begin to traverse through recordsets, you must understand two recordset properties: BOF and EOF. The names of these properties are outdated acronyms that stand for

beginning of file and

end of file , respectively. They determine whether you have reached the limits of your recordset. The BOF property is True when the record pointer is before the first record, and the EOF property is True when the record pointer is after the last record.

You commonly will use the EOF property when moving forward through your recordset with the MoveNext method. This property becomes True when your most recent MoveNext has moved you beyond the bounds of the recordset. Similarly, BOF is most useful when using the MovePrevious method.

You must keep in mind some important characteristics of the BOF and EOF properties:

• If a recordset contains no records, both the BOF and EOF properties evaluate to True.

• When you open a recordset containing at least one record, the BOF and EOF properties are set to False.

• If the record pointer is on the first record in the recordset and you issue the MovePrevious method, the BOF property is set to True. If you attempt to use MovePrevious again, a runtime error occurs.

• If the record pointer is on the last record in the recordset and you issue the MoveNext method, the EOF property is set to True. If you attempt to use MoveNext again, a runtime error occurs.

• When the BOF and EOF properties are set to True, they remain True until you move to a valid record.

• When the only record in a recordset is deleted, the BOF and EOF properties remain False until you attempt to move to another record.

Listing 14.15 shows an example of using the EOF property to determine the bounds of a recordset.

Listing 14.15 Using the EOF Property to Determine the Bounds of a Recordset

Sub DetermineLimits() 'Declare and instantiate a Recordset object Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset 'Establish the connection and cursor type and open 'the recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.Open "Select * from tblProjects" 'Loop through the recordset, printing the 'ClientID of each row Do Until rst.EOF Debug.Print rst("ClientID") rst.MoveNext Loop rst.Close End Sub

In Listing 14.15, a recordset is opened based on tblProjects. The EOF property is evaluated. As long as the EOF property equals False, the code prints the contents of the ClientID field, and advances the record pointer to the next record in the recordset.

Counting the Number of Records in a Recordset

The RecordCount property returns the number of rows in the recordset. Not all types of recordsets and providers support the RecordCount property. If the RecordCount property is not supported, no error occurs. Instead, the RecordCount is -1. Listing 14.16 provides an example.

Listing 14.16 A Recordset That Does Not Support the RecordCount Property

Sub CountRecordsBad() 'Declare and instantiate a recordset Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset 'Establish the connection and open a 'forward-only cursor rst.ActiveConnection = CurrentProject.Connection rst.Open "Select * from tblProjects" 'Print the RecordCount property Debug.Print rst.RecordCount 'Prints -1 rst.Close Set rst = Nothing End Sub

Because the default CursorType is adOpenForwardOnly, and a forward-only cursor does not support the RecordCount property, -1 prints to the Immediate window. Listing 14.17 rectifies this problem.

Listing 14.17 A Recordset That Supports the RecordCount Property

Sub CountRecordsGood() 'Declare and instantiate a recordset Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset 'Establish the connection and cursor type and open 'the recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.Open "Select * from tblProjects" 'Print the RecordCount property Debug.Print rst.RecordCount 'Prints Record count rst.Close Set rst = Nothing End Sub

Notice that the CursorType is set to adOpenStatic. Because the RecordCount property is supported with static cursors, the correct number of records is printed to the Immediate window.

NOTE

If you are accustomed to the DAO RecordCount property, you might be surprised by the ADO RecordCount property. The DAO RecordCount returns only the number of

visited records in the recordset. This means that, in using DAO, you must move to the last record in the recordset to obtain an accurate record count. Although this step is unnecessary when using ADO, it is important to note that attempting to retrieve the RecordCount property might result in severe performance degradation. Whether obtaining the RecordCount degrades performance depends on the particular database provider.

One of the important uses of the RecordCount property is to determine whether a recordset contains any rows. Listing 14.18 illustrates this important use of the RecordCount property.

Listing 14.18 Checking to See Whether Records Are Returned in a Recordset

Sub CheckARecordset() 'Declare and instantiate the recordset Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset 'Establish the connection and cursor type and open 'the recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.Open "Select * from tblEmpty" 'Call a routine to determine if the recordset contains 'any records If Not AreThereRecords(rst) Then MsgBox "Recordset Empty...Unable to Proceed" End If rst.Close Set rst = Nothing End Sub Function AreThereRecords(rstAny As ADODB.Recordset) As Boolean 'Return whether or not there are any rows AreThereRecords = rstAny.RecordCount End Function

The CheckARecordset routine opens a recordset based on a table called tblEmpty, which contains no data. The CheckARecordset routine calls the AreThereRecords function, passing a reference to the recordset. The AreThereRecords function evaluates the RecordCount property of the recordset that it is passed. It returns False if the RecordCount is zero, and True if the RecordCount is nonzero.

Sorting, Filtering, and Finding Records

Sometimes it is necessary to sort, filter, or find data within an existing recordset. The Sort property, Filter property, and Find method of the Recordset object allow you to accomplish these tasks. The sections that follow cover these properties and this method.

Sorting a Recordset

The Sort property of the Recordset object allows you to sort data in an existing recordset. Listing 14.19 illustrates its use.

Listing 14.19 The Sort Property of the Recordset Object

Sub SortRecordset() Dim intCounter As Integer 'Declare and instantiate a recordset Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset 'Establish the connection and cursor location and open 'the recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorLocation = adUseClient rst.Open "Select * from tblTimeCardHours" 'Loop through the recordset, printing 'the contents of the DateWorked field Debug.Print "NOT Sorted!!!" Do Until rst.EOF Debug.Print rst("DateWorked") rst.MoveNext Loop 'Sort the recordset and then loop through 'it, printing the contents of the DateWorked field Debug.Print "Now Sorted!!!" rst.Sort = "[DateWorked]" Do Until rst.EOF Debug.Print rst("DateWorked") rst.MoveNext Loop rst.Close Set rst = Nothing End Sub

The code begins by opening a recordset based on the tblTimeCardHours table. The code prints the records in the recordset in their "natural" order. Next, the Sort property of the Recordset object sorts the data by the DateWorked field. Notice that the Sort property is set equal to a field. If you want to sort by more than one field, you must separate the field names with commas. When the records are once again printed, they appear in order by the DateWorked field.

NOTE

To sort in descending order, the field name must be followed by a space and then the keyword DESC.

Filtering a Recordset

Sometimes you might want to select a subset of the data returned in a recordset. The Filter property helps you to accomplish this task. Its use is illustrated in Listing 14.20.

Listing 14.20 The Filter Property of the Recordset Object

Sub FilterRecordset() 'Declare and instantiate a recordset Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset 'Establish the connection, cursor type, 'and lock type, and open the recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenKeyset rst.LockType = adLockOptimistic rst.Open "Select * from tblTimeCardHours" 'Loop through the recordset, printing the contents of 'the DateWorked field Debug.Print "Without Filter" Do Until rst.EOF Debug.Print rst("DateWorked") rst.MoveNext Loop 'Filter the recordset and then loop through it, printing the 'contents of the DateWorked field rst.Filter = "DateWorked >= #1/1/1995# and DateWorked <= #1/5/1995#" Debug.Print "With Filter" Do Until rst.EOF Debug.Print rst("DateWorked") rst.MoveNext Loop rst.Close Set rst = Nothing End Sub

This example opens a recordset based on tblTimeCardHours. The code prints the records without a filter applied. The Filter property is then set to limit the data to only those records with a DateWorked value between 1/1/1995 and 1/5/1995. The code prints the records in the recordset again.

NOTE

It is inefficient to build a large recordset and to then filter only those records that you need. If you know that you need only records meeting specific criteria, you should build a recordset using those criteria. The difference in performance can be profound, particularly when dealing with client/server data. In summary, you should use the Filter property only when you are initially dealing with a larger set of records and then need to perform an operation on a subset of the records.

TIP

To return to the complete recordset after a filter has been applied, set the Filter property to a zero-length string (") or to the vbNullString constant.

Finding a Specific Record in a Recordset

The Find method allows you to locate a particular record in the recordset. It is different from the Filter property in that all records in the recordset remain available. Listing 14.21 illustrates the use of the Find method.

Listing 14.21 The Find Method of a Recordset Object

Sub FindProject(lngValue As Long) Dim strSQL As String 'Declare and instantiate a recordset Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset 'Establish the connection and cursor type, 'and open the recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.Open "Select * from tblProjects" 'Attempt to find a specific project strSQL = "[ProjectID] = " & lngValue rst.Find strSQL 'Determine if the specified project was found If rst.EOF Then MsgBox lngValue & " Not Found" Else MsgBox lngValue & " Found" End If rst.Close Set rst = Nothing End Sub

TIP

Because the FindProject routine is found in more than one module, the routine must be executed as follows:

Call basADORecordsets.FindProject(1)

Preceding the name of the routine with the name of the module removes the ambiguity as to which FindProject routine to execute.

The example opens a recordset based on all the records in the tblProjects table. The Find method is used to locate the first record where the ProjectID is equal to a specific value. If the record is not found, the EOF property of the Recordset object is True.

NOTE

Unlike its DAO counterpart, ADO does not support the FindFirst, FindNext, FindPrevious, and FindLast methods. The default use of the Find method locates the

next record that meets the specified criteria. This means that, if the record pointer is not at the top of the recordset, records meeting the specified criteria might not be located. The SkipRows, SearchDirection, and Start parameters of the Find method modify this default behavior. The SkipRows parameter allows you to specify the offset from the current row where the search begins. The SearchDirection parameter allows you to designate whether you want the search to proceed forward or backward from the current row. Finally, the Start parameter determines the starting position for the search.

Working with Variables in Strings

When using the Find method, or when building a SQL statement in code, you must be cognizant of the delimiters to use. No delimiters are necessary when working with numeric values. For example:

Select * FROM tblClients WHERE ClientID = 1

You must use a pound symbol (#) when delimiting dates for Microsoft Access, like this:

Select * FROM tblClients WHERE IntroDate = #12/31/2001#

CAUTION

If your back-end database is Microsoft SQL Server, you must use an apostrophe to delimit dates.

The process of delimiting strings is somewhat more difficult than it initially seems. The basic process is to surround the string with apostrophes:

Select * FROM tblClients WHERE City = 'Oak Park'

This works unless there is an apostrophe in the string. Listing 14.22 provides the solution.

Listing 14.22 Handling Apostrophes Within Strings

Sub DelimitString() Dim strCompanyName As String 'Declare and instantiate a Recordset object Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset 'Ask for the company to locate strCompanyName = InputBox("Please Enter a Company") 'Set the ActiveConnection, CursorType, 'LockType, and CursorLocation properties of the recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.LockType = adLockOptimistic rst.CursorLocation = adUseServer 'Open the recordset, designating that the source 'is a SQL statement rst.Open Source:="Select * from tblClients " & _ "WHERE CompanyName = " & ReplaceApostrophe(strCompanyName), _ Options:=adCmdText 'Display a message as to whether the selected company 'was found If rst.EOF Then MsgBox strCompanyName & " NOT Found!" Else MsgBox rst("ClientID") End If rst.Close Set rst = Nothing End Sub Public Function ReplaceApostrophe(strCompanyName As String) As String 'Surround text with apostrophes and replace any 'apostrophes in the string with two apostrophes ReplaceApostrophe = "'" & _ Replace(strCompanyName, "'", "''") & "'" End Function

The code passes the string to a user-defined function called ReplaceApostrophe, which surrounds the string with apostrophes. If any apostrophes are found within the string, they are replaced with two apostrophes.

Using the AbsolutePosition Property

The AbsolutePosition property of the Recordset object sets or returns the ordinal position of the current row in the recordset. Its use is illustrated in Listing 14.23.

Listing 14.23 The AbsolutePosition Property of a Recordset Object

Sub FindPosition(lngValue As Long) Dim strSQL As String 'Declare and instantiate a recordset Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset 'Establish the connection and cursor type, 'and open the recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.Open "Select * from tblProjects" 'Attempt to find a specific project strSQL = "[ProjectID] = " & lngValue rst.Find strSQL 'If record is found, print its position If rst.EOF Then MsgBox lngValue & " Not Found" Else Debug.Print rst.AbsolutePosition End If rst.Close Set rst = Nothing End Sub

In the example, the Find method is used to locate a project with a specific ProjectID. If the project is found, the ordinal position of the record that is located is printed to the Immediate window.

Using the Bookmark Property

The Bookmark property of a Recordset object returns a variant variable that acts as a unique identifier for that particular record in the recordset. You can use the Bookmark property to save the current position and then quickly and easily return to it at any time. Listing 14.24 illustrates the use of a bookmark.

Listing 14.24 The Bookmark Property of a Recordset Object

Sub UseBookmark() Dim strSQL As String Dim vntPosition As Variant 'Instantiate and declare a recordset Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset 'Establish the connection and cursor type, 'and open the recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.Open "Select * from tblProjects" 'Store bookmark in a variant variable vntPosition = rst.Bookmark 'Perform some operation 'on the records in the recordset Do Until rst.EOF Debug.Print rst("ProjectID") rst.MoveNext Loop 'Return to the bookmarked record by setting 'the Bookmark property of the recordset to the 'value stored in the variant variable rst.Bookmark = vntPosition Debug.Print rst("ProjectID") rst.Close Set rst = Nothing End Sub

In the example, a unique identifier to the current record is stored into a variant variable. The code then loops through the remainder of the records in the recordset. When it is done, it sets the Bookmark property of the Recordset object equal to the unique identifier stored in the variant variable.

CAUTION

Not all recordsets support bookmarks. Whether a recordset supports bookmarks depends on the provider as well as the type of recordset created.

Running Parameter Queries

You will not always know the criteria for a recordset at design time. Fortunately, ADO allows you to supply parameters to the CommandText property of the Command object. Listing 14.25 provides an example.

Listing 14.25 Running a Parameter Query

Sub RunParameterQuery(datStart As Date, datEnd As Date) 'Declare Command and Recordset objects Dim cmd As ADODB.Command Dim rst As ADODB.Recordset 'Instantiate the Command object Set cmd = New ADODB.Command 'Establish the connection, command text, 'and command type of the Command object cmd.ActiveConnection = CurrentProject.Connection cmd.CommandText = "Select * from tblTimeCardHours " & _ "Where DateWorked Between ? and ?" cmd.CommandType = adCmdText 'Use the Execute method of the Command object to 'return results into the recordset object; notice that 'an array is passed to the Parameters parameter of 'the Command object Set rst = cmd.Execute(Parameters:=Array(datStart, datEnd)) 'Loop through the resulting recordset, printing the 'contents of the TimeCardID and DateWorked fields Do Until rst.EOF Debug.Print rst("TimeCardID"), rst("DateWorked") rst.MoveNext Loop rst.Close Set rst = Nothing Set cmd = Nothing End Sub

Notice that in the example, the CommandText property contains two question marks. Each of these is considered a parameter. The parameters are supplied when the Execute method of the Command object is used. Notice that the Parameters argument of the Execute method receives an array containing the parameter values. Note that unless you specify basADORecordsets.RunParameterQuery, you get an "ambiguous name detected" error.

Refreshing Recordset Data

You can use two methods to refresh the data in a recordset: Requery and Resync. The Requery method is roughly equivalent to once again opening the recordset. The Requery method forces the OLEDB provider to perform all the steps it performed when first creating the recordset. New rows are added to the recordset, changes to data made by other users are reflected in the recordset, and deleted rows are removed from the recordset. The Requery method requires significant resources to execute. The Resync method is much more efficient. It updates the recordset to reflect changes made by other users. It does not show added rows or remove deleted rows from the recordset.

Working with Persisting Recordsets

Using ADO, recordsets cannot only exist in memory, but can also be written to disk. A recordset written to disk is referred to as a

persisted recordset . Listing 14.26 illustrates how to persist a recordset to disk.

Listing 14.26 Persisting a Recordset

Sub PersistRecordset() Dim strFileName As String 'Prompt user for filename and path strFileName = InputBox("Please Enter Filename and Path") 'Declare and instantiate a Recordset object Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset 'Set the ActiveConnection, CursorType, 'and LockType properties of the recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.LockType = adLockOptimistic 'Open the recordset, designating that the source 'is a SQL statement rst.Open Source:="Select * from tblClients ", _ Options:=adCmdText 'Destroy existing file with that name On Error Resume Next Kill strFileName 'Save the recordset rst.Save strFileName, adPersistADTG rst.Close Set rst = Nothing End Sub

Notice that the Save method of the Recordset object is used to persist the recordset to disk. The Format parameter of the Save method allows you to designate whether you want to save the recordset in the Microsoft proprietary Advanced Data Tablegram (ADTG) format, or whether you want to save the recordset as XML. Listing 14.27 shows you how to read a persisted recordset.

Listing 14.27 Reading a Persisted Recordset

Sub ReadPersistedRecordset() Dim strFileName As String 'Prompt user for filename and path to read strFileName = InputBox("Please Enter Filename and Path") 'Ensure that the selected file exists If Len(Dir(strFileName)) = 0 Then MsgBox "File Not Found" Exit Sub End If 'Declare and instantiate a Recordset object Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset 'Set the ActiveConnection, CursorType, 'and LockType properties of the recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.LockType = adLockOptimistic 'Open the recordset, designating that the source 'is a SQL statement rst.Open Source:=strFileName, _ Options:=adCmdFile 'Loop through the recordset, printing ClientIDs Do Until rst.EOF Debug.Print rst("ClientID") rst.MoveNext Loop rst.Close Set rst = Nothing End Sub

After prompting the user for a filename, the code ensures that the designated file is found. It then opens a recordset, using the file as the source argument. The adCmdFile constant is used for the Options parameter of the Open method. The adCmdFile value notifies ADO that the source is a persisted recordset.