The Recordset Object


As you've seen, a recordset is a programmatic construct for working with records. You can base your records on a table, a row-returning query stored in the current project or in another database file, a SQL statement, or on a command that returns rows. What you can do with a recordset depends on its OLE DB provider and on native data source attributes for the Connection object or connection string associated with the recordset's ActiveConnection property.

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 rowset 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 row or any subset of rows from a full recordset. There are two methods for finding records: Find and Seek . They offer related but not identical benefits, and they have contrasting requirements. Historically, recordsets are non-persistent objects ”they normally exist only while they are open in a program. Access 2003 allows developers to persist recordsets to disk and then reopen them later.

Selected Recordset Properties

This section will introduce you to recordsets by describing some of their properties. I will briefly review a few properties of the Recordset . Use the Object Browser in the VBE to survey the full range of recordset properties and to further explore the properties that this section summarizes.

A recordset's ActiveConnection property lets your application tap an open connection to support a recordset. You can set this property after instantiating the object reference for the recordset. However, the recordset must be closed (or not open yet). Using this property simplifies your Open method statement for the recordset. Setting the recordset's ActiveConnection property to a previously opened Connection object eliminates the need for including a connection string as part of the recordset's Open method statement. When you set the ActiveConnection property before invoking the Open method, you do not even need to reference an existing connection in the Open method statement. In that case, the Connection object is implicitly created for you.

The cursor type is among the most basic features of a recordset. Use the CursorType property to designate a cursor for a recordset. The property setting determines the ways you can navigate through the recordset, the visibility of changes by other users, and the types of locks that you can impose on its records. 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. Users can see all changes to a database made by other users. Assign an intrinsic constant of adOpenDynamic to the CursorType property to specify this type of cursor.

  • Keyset    This cursor has many of the properties of a dynamic cursor, except it does not offer immediate access to records added by other users. Records deleted by other users are inaccessible, but they appear in the recordset with a marker. Invoke a recordset's Requery method to view records added by other users and to clear the deleted markers for records removed by other users. Assign an intrinsic constant of adOpenKeyset to the CursorType property to designate this type of cursor.

  • Static    This cursor is a snapshot of a recordset at a particular point in time. It allows bidirectional navigation. Changes to the database by other users are not visible. This type of cursor is suitable when you do not need information about updates by other users, such as reports from a specific moment in time. Use an intrinsic constant setting of adOpenStatic to create this type of cursor.

  • Forward-only    Sometimes called the fire-hydrant cursor, this type moves in one direction only 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. If you are changing a recordset's cursor type back to the default setting, assign adOpenForwardOnly to its CursorType property.

    Note  

    Developers migrating from DAO might be surprised to learn that the keyset cursor is not the default ADO cursor type. You must explicitly designate adOpenKeyset as the CursorType property if your application requires a keyset cursor.

The LockType property interacts with the cursor type because it controls how users can manipulate a recordset. One lock-type setting ( adLockReadOnly ) specifically matches forward-only cursors . That is, specifying adLockReadOnly implies a setting of adOpenForwardOnly for a cursor's CursorType property. The adLockReadOnly LockType property setting is the default lock type. Table 1-3 describes the four possible settings for the LockType property. The adLockBatchOptimistic setting is used specifically for transmitting a batch of changes from a disconnected recordset to a database.

Table 1.3: LockTypeEnum Members

Constant

Value

Behavior

adLockUnspecified

­1

Only for use with recordset clones . One of two possible clone lock-type settings; the other is adLockReadOnly .

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; use with the UpdateBatch method to propagate changes to a local cache back to a remote server.

Note  

A recordset's CursorType property setting interacts with its lock-type setting with lock type taking precedence over cursor type. If you designate a forward-only cursor type with a lock type other than read-only ( adLockReadOnly ), ADO overrides your CursorType setting. For example, ADO automatically converts a forward-only cursor type to a keyset cursor type if you designate optimistic locking.

ADO supports two ways to update data in a data source. When you specify an adLockOptimistic or adLockPessimistic setting for the LockType property, your application immediately updates a record whenever it invokes the Update method for a recordset. By using the adLockBatchOptimistic intrinsic constant for the LockType property, your application can save up changes to one or more records until the application issues an UpdateBatch method for a recordset. The UpdateBatch method then transfers all changes from a local record cache to disk. If the update does not succeed because of conflicts between the recordset's base row values and the corresponding values of the data source on disk, ADO generates a run-time error. Use the Errors collection to view warnings and the Filter property with an adFilterAffectedRecords setting to locate records with conflicts.

Use the CursorLocation property to invoke the Cursor Service for OLE DB. Set the CursorLocation property to the adUseClient intrinsic constant for a Recordset object or a Connection object. The CursorLocation property also enables the Seek method. You cannot use the Seek method to search a recordset unless its CursorLocation property equals the adUseServer intrinsic constant.

The Sort property for a recordset can affect the results of both the Find and Move methods. (See the "Finding Records" and "Seeking Records" sections in this chapter for more on these methods.) Through the Sort property, you can designate one or more fields that can determine the order in which rows display. The Sort property setting allows specification of an ascending or descending order for any field. The default is ascending order. The Sort property settings do not physically rearrange the underlying rows ”they merely determine the order in which a recordset makes its rows available.

The Filter property for a recordset defines a subset of the rows from an existing recordset. Although this property has specialized applications for database synchronization and batch updating of a remote data source, it can also be a simple alternative to defining a new recordset based on a SQL statement or other source. If you already have a recordset and you need only a subset for another purpose, this property can serve admirably. Filter rules can contain compound as well as simple criteria statements. Set a Filter property to adFilterNone to remove a filter setting from a recordset and restore the full set of original values.

Selected Recordset Methods

Recordset methods complement recordset properties as a means of manipulating the values in a recordset. This section offers a brief review of selected methods and will give you a general idea of the kinds of functions that methods enable you to perform with recordsets. Use the Object Browser to survey the total set of recordset methods. You can get detailed help for any method by clicking the Help button in the Object Browser window.

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 stored query, a SQL statement, a Command object, or a saved recordset file. Access 2003 additionally makes available the option of a URL or a Stream object as a potential source for a recordset. You can use the Open method's Options argument to designate the source type when you open a recordset. However, when designating a Stream object as the source for a recordset, you should not designate any other parameters.

Several methods enable recordset navigation. These navigation methods also allow you to specify the current record in a recordset, which is necessary or desirable for certain methods, such as the Find method. These four methods reflect functionality comparable to that of a standard built-in bound form:

  • 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 no index exists, 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 this method with a forward-only cursor generates a run-time error.

  • MoveNext     This method relocates the current record position one record forward (that is, in the direction of the recordset's final record). If the current record position is the last record, the recordset's end-of-file ( 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 from 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 movement toward the last record and a negative argument to specify movement 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, in which case you specify 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. Because it is much faster to retrieve records from local memory than from a provider's data store, you can speed record navigation by using a larger cache size. With a forward-only cursor and a larger cache size , you can actually enable backward scrolling 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 within the cache with the MovePrevious method when using a forward-only cursor. However, you can use the Move method with a negative argument to achieve backward scrolling.

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 Access 97 and earlier Access versions, the Access 2003 version of Find 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 explicitly move off that record to find a subsequent 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 ADO event programming sample you will see in Chapter 2 illustrates the use of these other Find arguments. Several requirements govern the operation of the Find method. You must set the current record in a recordset before invoking the Find method. The only required argument for the Find method is a criterion indicating what to search for. You can only search for a value in a single field at a time. The default start location for a search in a recordset is the current row. You can also specify the starting row by using the method's arguments. This approach removes the need to reposition the current row. Otherwise, you must set the current record in a recordset before invoking the Find method. You can use one of the Move method variations to set the current record. If the CursorLocation property is adUseClient , the Find method can dynamically create indexes for fields that do not have an index specified in the recordset source. These are dynamic indexes that do not permanently update the source for a recordset.

Note  

Although the Find method works only for one field at a time, ADO readily permits the development of subsets from a data source based on two or more fields. For example, you can open a recordset using a Select statement with compound criteria or filter an existing recordset with compound criteria.

The AddNew method inserts a new record into 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 invoking the Delete method. The deleted record remains current until you move away from it.)

The GetRows method copies a recordset's contents from disk to an array in memory. This can make retrieving recordset values much faster since an application can gather data from memory rather than reading it from a disk storage device. Optional parameters enable you to specify subsets of an original recordset for copying into memory. You can designate a starting row to indicate when to start copying records, and you can designate which columns to include in the memory-based version of a recordset.

The GetString method enables you to return rows from a recordset as a sequence of text lines. This method is especially convenient for displaying values from a recordset in the Immediate window. Use GetString so that you don't need a loop to pass a group of rows from a recordset to the Immediate window. Optional parameters let you restrict the number of returned rows as well as the row and column delimiters for the values from a recordset. You can also designate a special value to represent fields with Null values.

Printing Recordset Rows

Printing recordset values to the Immediate window is a good tutorial for processing recordsets. As you first start to work with ADO recordsets, you will frequently be interested in testing whether you populated them with the intended values. The following procedure, EasyLoop , successively prints all the rows of a recordset. A loop passes through all the records and prints the first two fields of each record, CustomerID and CompanyName .

 SubEasyLoop() Dimrst1AsADODB.Recordset     'Instantiateandopenrecordset Setrst1=NewADODB.Recordset Withrst1 .ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb;" .Open"Customers",,,,adCmdTable EndWith     'Loopthroughrecordset DoUntilrst1.EOF Debug.Printrst1.Fields(0),rst1.Fields(1) rst1.MoveNext Loop     'Cleanupobjects rst1.Close Setrst1=Nothing     EndSub 

Notice that the procedure has three parts . First, it declares and instantiates a recordset before opening the recordset based on the Customers table. This table is the source for the recordset. The ActiveConnection property setting points the recordset at the database containing this table. The Open method for the Recordset object makes the rows of the source available to the procedure. Notice there are three vacant parameter spaces between the recordset's source and the Options parameter, adCmdTable . Since the statement specifies an Options parameter, it must include placeholders for the intervening parameters of the ActiveConnection , CursorType , and LockType properties. The procedure uses an assignment statement for the recordset's connection property, which is then used as the default value for the ActiveConnection parameter in the Open method and the Connection object is implicitly created. By not including values for the CursorType and LockType settings, the procedure accepts the default values of adOpenForwardOnly and adLockReadOnly .

Note  

The EasyLoop sample and other samples in this book use the traditional approach of declaring and instantiating a recordset in two separate statements. This approach has merit in procedures with many lines of code because it allows deferral of the instantiation cost until you need the Recordset object in a procedure. However, for short procedures with just a few lines of code (as is typical of many applications), there is no substantial penalty to declaring and instantiating with a single line of code, such as Dim rst1 as New ADODB.Recordset . The same coding guidelines apply to declaring and instantiating other ADO objects. Using a single statement to replace two statements is frequently a benefit to developers.

The second part of the procedure loops through all the rows in the recordset while printing the first and second field values in each row to the Immediate window. The sample uses a Do loop to navigate through the recordset until reaching an EOF flag after the last record. The MoveNext method advances the cursor one row with each pass through the loop. The code demonstrates how to reference fields by their index number. The reference to the Fields collection for a recordset is not strictly necessary. For example, you can replace rst1.Fields(0) with rst1(0) . If you know the field names and prefer to use them, you can replace the column index numbers with names, such as rst1.Fields("CustomerID") or rst1("CustomerID") .

The third part of the procedure merely closes the Recordset object and sets its reference to Nothing . There is no need to close a Connection object since the code sample assigns a connection string rather than a Connection object to the recordset's ActiveConnection property.

One weakness of the EasyLoop procedure is that it prints only the values of the fields you specifically request. The EasyLoop2a procedure that follows circumvents this difficulty. No matter how many fields the data source for a recordset has, the procedure automatically prints all of them. In addition, it labels each value with its field name . The main trick to iterating through all the fields is to include a For Each Next loop inside the Do loop. The For Each Next loop iterates through the Fields collection for each row in the recordset while constructing a string that includes the field name and value for each field in the row. After passing through all the fields in the row, the code sample uses the Left function to trim the trailing two characters , which are the field delimiters added inside the For Each Next loop.

 SubEasyLoop2a() Dimrst1AsADODB.Recordset DimfldMyFieldAsADODB.Field DimstrForRowAsString     'Instantiateandopenrecordset Setrst1=NewADODB.Recordset rst1.Open"customers","Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb;"     'Loopthroughrecordsetandfieldswithinrows DoUntilrst1.EOF strForRow="" ForEachfldMyFieldInrst1.Fields strForRow=strForRow&_ fldMyField.Name&"="&fldMyField.Value&";" NextfldMyField strForRow=Left(strForRow,Len(strForRow)-2) Debug.PrintstrForRow&vbCrLf rst1.MoveNext Loop     'Cleanupobjects rst1.Close Setrst1=Nothing     EndSub 

Figure 1-4 shows the last two rows of output. You can scroll right in the Immediate window to view the whole record, but it would be more convenient if you could see the whole record without having to scroll the window.

click to expand
Figure 1.4: The Immediate window output from the EasyLoop2a procedure.

The following adaptation of the preceding code sample breaks the line for a row into lines of about 60 characters. An If ElseIf statement inserts as many as four vbCrLf dividers into each line for a customer. We cannot use a Select Case statement in this situation because the condition is a compound one based on independent numeric and Boolean values. The procedure resets the Boolean values to their default values of False after processing each customer.

 SubEasyLoop2b() Dimrst1AsADODB.Recordset DimfldMyFieldAsField DimstrForRowAsString DimbolGT60AsBoolean DimbolGT120AsBoolean DimbolGT180AsBoolean DimbolGT240AsBoolean     'Instantiateandopenrecordset Setrst1=NewADODB.Recordset rst1.Open"customers","Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb;"     'Loopthroughrecordsetandfieldswithrows. 'InsertvbCrLFafterexceedingeachmultipleof60characters. DoUntilrst1.EOF strForRow="" bolGT60=False bolGT120=False bolGT180=False bolGT240=False ForEachfldMyFieldInrst1.Fields strForRow=strForRow&_ fldMyField.Name&"="&fldMyField.Value&";" IfLen(strForRow)>60AndbolGT60=FalseThen strForRow=strForRow&vbCrLf bolGT60=True ElseIfLen(strForRow)>120AndbolGT120=FalseThen strForRow=strForRow&vbCrLf bolGT120=True ElseIfLen(strForRow)>180AndbolGT180=FalseThen strForRow=strForRow&vbCrLf bolGT180=True ElseIfLen(strForRow)>240AndbolGT240=FalseThen strForRow=strForRow&vbCrLf bolGT240=True EndIf NextfldMyField strForRow=Left(strForRow,Len(strForRow)-2) Debug.PrintstrForRow&vbCrLf rst1.MoveNext Loop     'Cleanupobjects rst1.Close Setrst1=Nothing     EndSub 

Figure 1-5 shows the improvement in the formatting of the output to the Immediate window.

click to expand
Figure 1.5: The Immediate window output from the EasyLoop2b procedure.

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 field values from a recordset. The NoEasyLoop procedure that follows uses the GetString method to retrieve and print all the fields from the first five rows of a recordset in one step:

 SubNoEasyLoop() Dimrst1AsADODB.Recordset     'Instantiateandopenrecordset Setrst1=NewADODB.Recordset rst1.Open"customers",_ "Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb;"     'Printrecordswithoutaloop Debug.Printrst1.GetString(adClipString,5,";")     'Cleanupobjects rst1.Close Setrst1=Nothing     EndSub 

The GetString method returns a recordset as a string. It can take up to five arguments; the code uses three of those arguments. According to the documentation, you must designate the adClipString constant as the first argument in order to assign values to the other optional arguments, which designate the column delimiters, row delimiters, and expressions for Null values. The second argument specifies the number of recordset rows to return. The preceding code sample requests the return of five rows. Leaving the second argument blank tells the method to return all the rows in the recordset. The third argument in the code sample designates a semicolon followed by a space as the column delimiter. The default column delimiter is a tab. The fourth and fifth arguments, neither of which appears in the code, specify a row delimiter and a representation for Null values. The default values for these arguments are a carriage return and a zero-length string.

Note  

You can omit assigning a value of adClipString to the first argument and still specify values for row and column delimiters, as well as representations for Null values.

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 of code.

Another method, GetRows , offers several benefits when retrieving recordset values. First, it captures the recordset values to memory in an array. This makes it possible to delete a Recordset object and still work with its values. At the very least, this reduces the resource load of an application. In Web applications where a server collects the data for many users, this advantage can be important. Second, you can achieve performance gains by working with an array in memory rather than on disk. These gains depend on the recordset being sufficiently small to fit in physical memory. Third, the ADO GetRows method syntax lets you specify the number of rows to retrieve, the row from which to start retrieving, and the fields to retrieve from each row. Because you are storing the recordset values in memory, you should include just the minimum set of rows and fields necessary for your needs.

In using the GetRows method, you must literally reorient your way of thinking about the data in a recordset. This is because GetRows transposes the rows and columns from disk storage to a two-dimensional Variant array. When you declare the Variant array for the method's results set, you do not need to specify the number of rows or columns for the array. However, when retrieving data from the array, you must remember that recordset rows populate columns (instead of rows) in the Variant array.

The GettingRows procedure shown next demonstrates a couple of approaches to retrieving recordset contents with the GetRows method. The first application invokes the method with no arguments. This approach reveals just how simple it is to capture a recordset to memory with the GetRows method. The syntax for this approach copies all the rows and all their columns from disk to memory. The loop for printing values to the Immediate window shows the syntax for iterating through copied recordset rows as columns in the array.

 SubGettingRows() Dimrst1AsADODB.Recordset DimvarArrayAsVariant Dimint1AsInteger     'Instantiateandopenrecordset Setrst1=NewADODB.Recordset rst1.Open"customers","Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb;"     'CopyallrowswithallcolumnstovarArrayinmemory varArray=rst1.GetRows     'Loopthrougharraybasedonrecordset,printingfirsttwofields Forint1=0ToUBound(varArray,2) Debug.PrintvarArray(0,int1),varArray(1,int1) Nextint1     'Restorethepointertothefirstrecord,andcopy 'thefirstfiverowswithCustomerIDandCompanyName 'fieldsintovarArrayinmemory rst1.MoveFirst varArray=rst1.GetRows(5,,_ Array("CustomerID","CompanyName"))     'Loopthrougharraybasedonrecordset Forint1=0ToUBound(varArray,2) Debug.PrintvarArray(0,int1),varArray(1,int1) Nextint1     'Cleanupobjects rst1.Close Setrst1=Nothing     EndSub 

The second application of the GetRows method in the GettingRows procedure demonstrates just how easy it is to specify a subset of the rows to copy to memory. The code instructs the ADO interpreter to copy only five rows from disk to memory. These rows are from the top of the recordset because the use of the MoveFirst method precedes the use of the second GetRows method. By default, the GetRows method copies records starting from the current row. However, you can designate a bookmark for another record as the second argument if you want the method to start from a location other than the current row. The second application of GetRows also illustrates how to use the built-in Array function to specify a subset of fields to copy from disk to memory. In particular, it copies just the CustomerID and CompanyName column values from the Customers table.

Note  

Often you will want to start copying with the GetRows method from the first row, even when the current row has a different position. In this situation, use the intrinsic constant adBookmarkFirst for the method's second parameter to point to the first row.

Adding, Editing, and Deleting Records

The preceding set of samples for printing recordsets all accepted the default settings for the CursorType and LockType properties. Recall that the default settings are forward-only and read-only. In other words, you cannot change existing rows in a recordset or add new ones with these default settings. When all you want to do is print recordset values, these property settings are fast and efficient. However, by overriding these default settings to change the cursor type to keyset with optimistic locking, you can readily modify the field values within the rows of a recordset. Because the samples in this section all modify recordsets, they all use these non-default settings.

Note  

All the samples in this section use optimistic locking for adding, editing, and deleting records. Either optimistic or pessimistic locking is necessary with a keyset cursor. Optimistic locking offers slightly better throughput along with the risk of concurrency violations when two users attempt to modify a record at the same time. ADO returns an error that permits you to detect this kind of error and respond accordingly , such as to resubmit the change that generated the concurrency violation. If you prefer, change the optimistic locking to pessimistic locking to remove the possibility of concurrency violations.

Adding a Row

One way to modify a recordset is by adding a new row of field values. The AddAShipper function procedure shown next demonstrates an approach to this task using the AddNew method for an ADO recordset. The sample applies this method to a recordset based on the Shippers table in the Northwind database. This table has an AutoNumber field named ShipperID and two string fields named CompanyName and Phone . Since Access automatically assigns AutoNumber field values, the procedure only has to assign values to the CompanyName and Phone fields. The procedure inserts these assignments between a statement that invokes the AddNew method and another one that launches the Update method.

 FunctionAddAShipper() Dimrst1AsADODB.Recordset     'Setyourcursorsothatitisnotread-only 'inordertoaddarow Setrst1=NewADODB.Recordset rst1.ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb;" rst1.Open"Shippers",,adOpenKeyset,adLockOptimistic,_ adCmdTable     'InvoketheAddNewmethod Withrst1 .AddNew .Fields("CompanyName")="Access2003Delivers" .Fields("Phone")="(555)123-4567" .Update EndWith     'ReturnShipperIDofnewshipper AddAShipper=rst1("ShipperID")     'Cleanupobjects rst1.Close Setrst1=Nothing     EndFunction 

Because the AddAShipper sample uses a function procedure, it can return a value, such as the ShipperID value that Access assigns automatically. This AutoNumber field is the primary key for the Shippers table. If your application inserts records into both a main table and a related table, such as Orders and Order Details , the AutoNumber primary key field value from the main table would be essential for setting the foreign key values in the related table.

Deleting a Row

Another task that you likely will want to perform with recordsets is deleting records. The following pair of procedures shows one approach to this task. While this approach might not be optimal in some respects, it has the benefit of not requiring any special SQL syntax or even advanced ADO functionality. Basically, you loop through a recordset until you find a match. Then, you just delete the matching record.

The code sample in the CallDeleteARecordsetRow and DeleteARecordsetRow procedures lets a user specify any field as a criterion for the row to delete. If a recordset contains multiple rows with the same field value, it deletes just the first of these rows. The CallDeleteARecordsetRow procedure includes the code to delete a row based on CompanyName , Phone , or ShipperID field values. The sample leaves commented only the syntax for deleting a row based on Phone .

 SubCallDeleteARecordsetRow() Dimrst1AsADODB.Recordset Dimint1AsInteger     'Setyourcursorsothatitisnotread-only 'inordertodelete Setrst1=NewADODB.Recordset rst1.ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb;" rst1.Open"Shippers",,adOpenKeyset,adLockOptimistic,_ adCmdTable     'DeleteashipperindexedbyCompanyNameorPhone DeleteARecordsetRowrst1,"CompanyName","Access2003Delivers" 'DeleteARecordsetRowrst1,"Phone","(555)123-4567"     'Requeryrecordsettoclearrowmarkedfordeletion rst1.Requery     'DeleteashipperindexedbyShipperID 'sixsecondsafteraddingit int1=AddAShipper() DoLoopForSeconds6 DeleteARecordsetRowrst1,"ShipperID",int1     'Cleanupobjects rst1.Close Setrst1=Nothing     EndSub     SubDeleteARecordsetRow(rst1AsADODB.Recordset,_ FieldNameAsString,FieldValueAsVariant)     'Designaterst1asrepeatedvaluein 'With...EndWithstatement Withrst1 'Loopthroughrecordsettofindtarget 'fieldvalueforselectedfield DoUntil.EOF If.Fields(FieldName)=FieldValueThen .Delete ExitDo EndIf .MoveNext Loop EndWith     EndSub     SubDoLoopForSeconds(nAsInteger) DimBeginAsDate     'SetupforDoloop Begin=Now() Debug.Print"Timebeforeloop:"&Begin     'Loopuntilnsecondsfrombegintime Do IfDateDiff("s",Begin,Now())>=n_ ThenExitDo Loop     'Printendtimeandbeep Debug.Print"Timeafterloop:"&Now() Beep     EndSub 

The calling procedure, CallDeleteARecordsetRow , passes a recordset, field name, and a field value. The called procedure, DeleteARecordsetRow , loops through the recordset. If the loop uncovers a row with a field value that matches the input parameters, it deletes the row and exits the loop. After the deletion, the calling procedure invokes the Requery method for the recordset to synchronize its copy with the one in the database.

The sample enables you to delete a recordset row based on the ShipperID field by adding a new row and saving the ShipperID value for that new row. It does this by saving the return value of the AddAShipper function procedure in a memory variable named int1 . Then, it uses the value of that memory variable as an argument when it calls DeleteARecordsetRow . This aspect of the sample demonstrates how to use the value of an automatically added primary key to control another action. In this case, the other action is to delete a row, but it could just as easily be to insert rows in a related table. The sample launches a six-second pause between adding a new row and attempting to delete it (based on ShipperID ) with the help of a call to the DoLoopForSeconds procedure. This pause is necessary to allow the added row to update the Access database.

The DoLoopForSeconds procedure includes a Do loop without conditions for the Do or Loop keywords. Instead, the loop exits when the current time is n seconds or beyond the time just before entering the loop. A DateDiff function computes the difference between the loop start time and the current time within the loop in seconds. The procedure beeps to mark the end of the n -second interval.

Editing a Row

The third sample in this section demonstrates how to update an existing field value in a recordset row with a new field value. The calling procedure, CallUpdateAFieldValue , calls four procedures in this sample.

  • The first call is to the AddAShipper function to add a new row to the Shippers table for editing.

  • Another call to the DoLoopForSeconds procedure pauses for the database to perform the insert.

  • Next, CallUpdateAFieldValue passes four arguments to the UpdateAFieldValue procedure. In this case, the passed parameters are the recordset, the name of the field to update, and the old and new field values. The UpdateAFieldValue procedure loops through the recordset's rows until it finds a match. Then it assigns the new field value and invokes the Update method for the recordset. This commits the changed value to disk. After performing the update, the sub procedure exits the loop and restores control to the calling routine. If multiple records have field values that match the OldFieldValue input parameter, the sub procedure updates just the first record. You can update all the rows with matching values by removing the Exit Do statement inside the loop.

  • A final call to the DeleteARecordsetRow procedure removes the row from the Shippers table with the CompanyName field value updated by the UpdateAFieldValue procedure.

     SubCallUpdateAFieldValue() Dimrst1AsADODB.Recordset Dimint1AsInteger     'Setyourcursorsothatitisnotread-only 'inordertoudpatearecord. Setrst1=NewADODB.Recordset rst1.ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice"&_ "\Office11\Samples\Northwind.mdb;" rst1.Open"Shippers",,adOpenKeyset,adLockOptimistic,_ adCmdTable     'Addnewrowandwait6secondsfordatabasetoupdatetable. int1=AddAShipper() DoLoopForSeconds6     'Callupdatingroutine,andpassitupdateinfo. UpdateAFieldValuerst1,"CompanyName","Access2003Delivers",_ "Access2003DeliversMore"     'Deleteupdatedversionofinsertedrowtorestore 'theShipperstable. DeleteARecordsetRowrst1,"CompanyName","Access2003DeliversMore"     'Cleanupobjects. rst1.Close Setrst1=Nothing     EndSub     SubUpdateAFieldValue(rst1AsADODB.Recordset,_ FieldNameAsString,_ OldFieldValueAsVariant,_ NewFieldValueAsVariant)     'Designaterst1asrepeatedvaluein 'With...EndWithstatement Withrst1 'Loopthroughrecordsettofindtarget 'fieldvalueforselectedfield DoUntil.EOF If.Fields(FieldName)=OldFieldValueThen .Fields(FieldName)=NewFieldValue .Update ExitDo EndIf .MoveNext Loop EndWith EndSub 

Finding Records

Another common task with a recordset is to find one or more records that meet specified criteria. Access offers several approaches to this task. Before Access 2000, many developers used one or more variations of the Find method in DAO. Access 2000 introduced a single Find method for ADO that consolidates the functionality of multiple DAO Find methods. If your applications used the earlier Find methods, you can achieve the same results with the consolidated method in Access 2000. Access 2003 has new Find method capabilities unavailable to Access developers with Access 2000.

Note  

The Find method, along with the Seek method and Filter property, allows you to re-use an existing recordset rather than create a new recordset to recover rows your application needs. It is generally more efficient to re-use an existing recordset than to declare, instantiate, and open a new one.

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 , ContactName , and Phone fields to confirm exactly which record matches the criterion.

 SubFindAMatch() Dimrst1AsRecordset     'Instantiateandopenrecordset Setrst1=NewADODB.Recordset rst1.ActiveConnection=_ "Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice"&_ "\Office11\Samples\Northwind.mdb;" rst1.Open"Customers",,adOpenKeyset,adLockPessimistic,_ adCmdTable     'FindthefirstrowwithaCustomerIDbeginningwithD, 'andprinttheresult rst1.Find("CustomerIDLike'D*'") Debug.Printrst1("CustomerID"),rst1("ContactName"),rst1("Phone")     'Cleanupobjects rst1.Close Setrst1=Nothing     EndSub 

One drawback to the FindAMatch procedure is that it searches for a single match to the criteria and then stops immediately after finding it. The code that follows, which shows the FindAMatch2 procedure, discovers all the records that match the criterion statement. This simple application reveals more of the flexibility of the Find method:

 SubFindAMatch2() Dimrst1AsRecordset 'Instantiateandopenrecordset Setrst1=NewADODB.Recordset rst1.ActiveConnection=_ "Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice"&_ "\Office11\Samples\Northwind.mdb;" rst1.Open"Customers",,adOpenKeyset,_ adLockPessimistic,adCmdTable     'Openaninfiniteloopforallrecordsmatching 'thecriterion Do rst1.Find("CustomerIDLike'D*'") Ifrst1.EOFThen 'Exittheprocedurewhennomorematchesexist ExitDo EndIf Debug.Printrst1.Fields("CustomerID"),rst1("ContactName"),_ rst1("Phone") rst1.MoveNext Loop     'Cleanupobjects rst1.Close Setrst1=Nothing     EndSub 

The trick to finding all the records that match the search criterion is to embed the Find method in an infinite 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 Do statement to exit the loop. As long as Find keeps discovering new matches, the procedure prints CustomerID , ContactName , and Phone fields 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.

One innovation available to those using Access 2003 is the dynamic index capability of the Cursor Service. Access 2003 ships natively with an ADO version that supports dynamic indexes. To speed the operation of the Find method, developers can create dynamic indexes on the fly for record sources that do not have an index on a search field. Creating a dynamic index is a two-step process. First, assign the adUseClient intrinsic constant to the recordset's CursorLocation property. Second, set the Optimize property to True in the Properties collection so that the field will gain the dynamic index. The index goes out of scope when you close the procedure, but you can force the index's removal by setting the field's Optimize property to False .

Note  

You can acquire the dynamic index feature in Access 2000 by installing a more recent ADO version than the one that initially shipped with Access 2000.

The following pair of procedures demonstrates how to use a dynamic index to speed the search for all the customers from a particular country. The first procedure, CallFindCustomersInACountry , assigns a search string value and passes it to the second procedure, FindCustomersInACountry . Notice that the sample code uses a pound sign (#) to delimit the country string within the criterion string. With Access 2003, you can use either # or an apostrophe (') for this purpose.

Before opening a recordset on the Customers table, the second procedure turns on the Cursor Service by setting the CursorLocation property to adUseClient . Then after the recordset is open, the procedure sets the Country field's Optimize priority to True . This causes the creation of the index. Next, the procedure performs a normal search to find all the customers from the country designated by strCriterion . In the cleanup process for the procedure, there is nothing special you need to do with the index. It goes out of scope automatically.

 SubCallFindCustomersInACountry() DimstrCriterionAsString     'Setstringforcountrycriterion,andpassit 'totheroutinetodothesearch strCriterion="Country=#USA#" FindCustomersInACountrystrCriterion     EndSub     SubFindCustomersInACountry(strCriterionAsString) Dimrst1AsADODB.Recordset Dimint1AsInteger     'Instantiateandopenrecordset;invoketheCursorServiceby 'settingtheCursorLocationpropertytoadUseClient Setrst1=NewADODB.Recordset Withrst1 .ActiveConnection=_ "Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice"&_ "\Office11\Samples\Northwind.mdb;" .CursorLocation=adUseClient .Open"customers",,adOpenKeyset,_ adLockPessimistic,adCmdTable EndWith     'CreateadynamicindexonCountry rst1.Fields("Country").Properties("Optimize")=True     'FindthelongestContactNamefield,andaddonetoit int1=FindTheLongest(rst1,"ContactName")+1     'Openaninfiniteloopforallrecordsmatching 'thecriterion rst1.MoveFirst Do rst1.Find(strCriterion) Ifrst1.EOFThen 'Exittheprocedurewhennomorematchesexist ExitSub EndIf Debug.Printrst1.Fields("CustomerID"),rst1("ContactName")&_ String(int1-Len(rst1("ContactName")),"")&""&rst1("Phone") rst1.MoveNext Loop     'Cleanupobjects rst1.Close Setrst1=Nothing     EndSub 

Seeking Records

The Seek method is another recordset method that can facilitate finding records in a record source. This method is exclusively used for recordsets that specify a table as their source. In addition, you must set the recordset Open method's Options parameter to adCmdTableDirect , instead of setting it to adCmdTable or leaving it blank. Furthermore, the table serving as a record source must have an index on the field or fields that your application searches to find matches for criterion values. ADO does not offer built-in support for creating dynamic indexes for the Seek method, but your application can programmatically create an index using the ADOX library for Jet and the SQL-DMO library for SQL Server. The Seek method also requires a server-side cursor. Using any value other than adUseServer for a recordset's CursorLocation property disables the method.

Note  

The Seek method is not a core ADO data access feature. ADO providers can offer it as an option. For example, the Jet provider enables the Seek method, but the SQL Server provider does not. In addition, the Jet provider makes the Seek method available only when you open a recordset properly. Use the recordset Supports method with adSeek as an argument to return a Boolean indicating whether a provider offers the Seek method. To seek on an index, apply the method to a recordset after opening it.

The Seek method searches for a key value or values, and it optionally takes a SeekOption parameter that can guide the operation of the method. If the index for the Seek method has just one column, you can specify only a single criterion value. If the index for a recordset relies on multiple columns, use an Array function to specify values for all the columns in the index. This capability to search concurrently on multiple columns is one feature distinguishing the Seek method from the Find method. The Seek method makes available the recordset in the order of the index and positions the current record at the first record matching the criteria.

You can refine the search behavior of the Seek method with some settings for its SeekOptions parameter using a member from the SeekEnum . The SeekOptions setting appears immediately after the key value or values for a Seek method. The syntax for the Seek method with both key values and SeekOptions is rst.Seek < Keyvalues >, < SeekOptions >. Table 1-4 lists the SeekEnum members with their values and behavior.

Table 1.4: Intrinsic Constants for the SeekEnum

Constant

Value

Behavior

adSeekFirstEQ

1

Seek the first record on the index or indexes matching the key value or values.

adSeekLastEQ

2

Seek the last record on the index or indexes matching the key value or values.

adSeekAfterEQ

4

Seek the first record (or the one after it when no match exists) on the index or indexes matching the key value or values.

adSeekAfter

8

Seek the first record after that potential match to the key value or values on the index or indexes.

adSeekBeforeEQ

16

Seek the first record (or the one before it when no match exists) on the index or indexes matching the key value or values.

adSeekBefore

32

Seek the first record before a potential match to the key value or values on the index or indexes.

The SeekingUnShippedOrders procedure that follows illustrates three ways to use the Seek method. Before actually invoking the Seek method, your application must properly prepare the recordset. First, assign the Index property so that it contains the values for which you want to search. Second, open the recordset with the adCmdTableDirect setting for the Options parameter. Third, ensure the CursorLocation property has a setting of adUseServer . This is the default setting, so you don't need to assign the property a setting unless your application has changed its default setting. The following procedure demonstrates the first two steps. The third step is not necessary in this sample.

 SubSeekingUnshippedOrders() Dimrst1AsADODB.Recordset     'Instantiateandopenrecordset;Seekmethodrequires 'indexassignment,adCmdTableDirectionOpenoption,and 'adUserServersettingforCursorLoction(default) Setrst1=NewADODB.Recordset rst1.ActiveConnection=_ "Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice"&_ "\Office11\Samples\Northwind.mdb;" rst1.CursorType=adOpenKeyset rst1.LockType=adLockOptimistic rst1.Index="ShippedDate" rst1.Open"Orders",,,,adCmdTableDirect     'Printfirstordernotshipped rst1.SeekNull,adSeekFirstEQ Debug.Printrst1("OrderID"),rst1("OrderDate")     'Printlastordernotshipped rst1.SeekNull,adSeekLastEQ Debug.Printrst1("OrderID"),rst1("OrderDate")     'Printallordersnotshipped rst1.SeekNull Do Debug.Printrst1("OrderID"),rst1("OrderDate") rst1.MoveNext LoopUntilIsNull(rst1("ShippedDate"))=False     'Cleanupobjects rst1.Close Setrst1=Nothing     EndSub 

The first two instances of the Seek method in SeekingUnShippedOrders both demonstrate the syntax for the Keyvalues and SeekOptions parameters. The first instance of the Seek method sets the cursor at the first record in the Orders table that is unshipped. This record has the order ID 11008. The steps for opening the rst1 recordset on the Orders table arrange the records in order on the ShippedDate index, so that the special ordering indicated by the Seek method's Keyvalues and SeekOptions parameters will work. The second instance of the Seek method in the SeekingUnShippedOrders procedure sets the cursor at the last Orders table record with a Null value for the ShippedDate field. This record has the order ID 11077. The third instance of the Seek method in the preceding procedure searches for all unshipped orders without specifying a SeekOptions setting. Its following Do loop iterates through the reordered recordset to print the OrderID field and OrderDate field for each record with a Null ShippedDate field value.

The SeekWith2IndexValues procedure demonstrates the syntax for designating a seek for two criterion values: the OrderID and ProductID fields of the primary key for the Order Details table. The procedure also demonstrates how to reuse a Recordset object. In the first use of the rst1 recordset, the procedure searches for all line items matching an OrderID input by the user. The procedure uses an InputBox function nested inside a CInt function to permit a user to input an OrderID field value. It then returns the OrderID and ProductID for the line items associated with that record. This first seek relies on the OrderID index for the Order Details table.

 SubSeekWith2IndexValues() Dimrst1AsADODB.Recordset Dimint1AsInteger Dimint2AsInteger Dimint3AsInteger     'Instantiateandopenrecordset;Seekmethodrequires 'indexassignment,adCmdTableDirectionOpenoption,and 'adUserServersettingforCursorLocation(default) Setrst1=NewADODB.Recordset rst1.ActiveConnection=_ "Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice"&_ "\Office11\Samples\Northwind.mdb;" rst1.CursorType=adOpenKeyset rst1.LockType=adLockOptimistic rst1.Index="OrderID" rst1.Open"OrderDetails",,,,adCmdTableDirect     'Searchforauser-specifiedOrderID; 'saveOrderIDandProductIDoforder'slastlineitem int1=CInt(InputBox("InputOrderID:")) rst1.Seekint1 DoUntilrst1("OrderID")<>int1 Debug.Printrst1("OrderID"),rst1("ProductID") rst1.MoveNext Ifrst1.EOFThenExitDo Loop rst1.MovePrevious int2=rst1("OrderID") int3=rst1("ProductID")     'CloseandreopenOrderDetailstoseek 'lastlineitemintheorder rst1.Close rst1.Index="PrimaryKey" rst1.Open"OrderDetails",,,,adCmdTableDirect     rst1.SeekArray(int2,int3) Debug.Printrst1("OrderID"),rst1("ProductID"),_ FormatCurrency(rst1("UnitPrice")),_ rst1("Quantity"),FormatPercent(rst1("Discount"),0)     'Cleanupobjects rst1.Close Setrst1=Nothing     EndSub 

The second instance of the Seek method in the SeekWith2IndexValues procedure seeks the last item for the OrderID that a user inputs. At the conclusion of the first Seek instance, the procedure saves the OrderID and ProductID field values for the last line item in the order a user referenced. Then the procedure closes the recordset to assign a new index. The index is the primary key that relies on both OrderID and ProductID . Next, the procedure reopens the recordset. The new index is critical for permitting the use of a compound criterion that includes values for both the OrderID and ProductID field values. Notice the use of the Array function for indicating more than one key value. The cursor points to the record in the recordset for the line item sought. The procedure prints it while formatting the UnitPrice and Discount columns.

Filtering Records

The Find method can go through a recordset sequentially and disclose matches one at a time. The method does not create another version of the recordset that contains all the records that match the criterion values. When you need a new or alternate recordset containing just the matches, your application needs a different approach. The Seek method offers a partial solution to this Find method deficiency, but the Seek method has special requirements that restrict its applicability, such as the need for an index. In addition, the Seek method does not truly exclude non-matching records. It merely positions the current record at the first matching record.

The recordset Filter property offers a second solution to the Find method's inability to return a subset of a recordset based on one or more fields. 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 value or values. By setting the Filter property to any of a series of constants instead of to a criterion string, 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 rowset. The Filter property also resolves another shortcoming of the Find method: it can select records based on more than one field value. Furthermore, it does not require an index to accomplish this goal, as does the Seek method.

The following two 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 results set, clears the filter, and then prints the results set again. The FilterRecordset procedure relies on the FilterLikeField function. This custom function manages the setting of the Filter property based on parameters passed to it by the FilterRecordset procedure, and it returns the filtered recordset.

 SubFilterRecordset() Dimrst1AsADODB.Recordset     'Instantiateandopenrecordset Setrst1=NewADODB.Recordset rst1.ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice"&_ "\Office11\Samples\Northwind.mdb;" rst1.Open"Customers",,,,adCmdTable     'Filterrecordset,andprintfilteredresult Setrst1=_ FilterLikeField(rst1,"CustomerID","D*") Debug.Printrst1.GetString     'Restorerecordset,andprintrestoredresult rst1.Filter=adFilterNone Debug.Printrst1.GetString     'Cleanupobjects rst1.Close Setrst1=Nothing     EndSub     FunctionFilterLikeField(rst1AsADODB.Recordset,_ strFieldAsString,strFilterAsString)AsADODB.Recordset     'SetafilteronthespecifiedRecordsetobject,andthen 'returnthefilteredrecordset rst1.Filter=strField&"LIKE'"&strFilter&"'" SetFilterLikeField=rst1     EndFunction 

The FilterRecordset procedure starts by creating and opening the rst1 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 rst1 and prints the filtered recordset to confirm the result.

The arguments to FilterLikeField include rst1 , 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 restricts your criteria expressions to those of the form < FieldName > < Operator > < Value >. However, some Filter intrinsic constants that are members of the FilterGroupEnum enum enable special uses of the property. The FilterRecordset procedure uses the adFilterNone constant to restore a recordset by removing its filters. You can also use other FilterGroupEnum enum members to resolve conflicts associated with performing a batch update against a remote data source.

The following pair of procedures demonstrates just how easy it is to filter on multiple criterion fields. You can compound simple filter phrases, such as "Country='USA'" , with AND and OR operators. This capability permits you to filter a recordset on more than one field. You can also group criteria expressions with parentheses and then link them with AND and OR operators for the construction of complex compound criteria. This next sample shows a compound criteria expression that does not require parentheses.

The sample demonstrating compound filters uses two procedures. The first procedure, CallFilterOnCountryAndCity , sets the criteria strings for both country and city, the two filter fields for the Customers table. Then the sample calls FilterOnCountryAndCity , the procedure that applies the filter, and it saves a reference to the filtered recordset. The main procedure concludes by printing selected fields. Before printing field values, however, it uses the FindTheLongest function procedure to determine the longest length for each of two fields.

 SubCallFilterOnCountryAndCity() Dimrst1AsADODB.Recordset DimstrCountryAsString DimstrCityAsString Dimint1AsInteger Dimint2AsInteger     'SetCountryandCitycriteria strCountry="USA" strCity="Portland"     'CallFilterroutine,andstorereferenceto 'filteredreturnset Setrst1=FilterOnCountryAndCity(strCountry,strCity)     'Getthelongestlengthsforthe 'ContactNameandCompanyNamefields int1=FindTheLongest(rst1,"ContactName")+1 rst1.MoveFirst int2=FindTheLongest(rst1,"CompanyName")+1     'Printselectedfieldsfromfilteredrecordset rst1.MoveFirst DoUntilrst1.EOF Debug.Printrst1("ContactName")&_ String(int1-Len(rst1("ContactName")),"")&_ rst1("CompanyName")&_ String(int2-Len(rst1("CompanyName")),"")&_ rst1("City"),rst1("Country") rst1.MoveNext Loop     'Cleanupobjects rst1.Close Setrst1=Nothing     EndSub     FunctionFilterOnCountryAndCity(strCountryAsString,_ strCityAsString)AsADODB.Recordset Dimrst1AsADODB.Recordset     'Instantiateandopenrecordset Setrst1=NewADODB.Recordset rst1.ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice"&_ "\Office11\Samples\Northwind.mdb;" rst1.Open"Customers",,adOpenKeyset,adLockOptimistic,adCmdTable     'SetFilterproperty rst1.Filter="Country='"&strCountry&_ "'ANDCity='"&strCity&"'"     'Returnfilteredrecordset SetFilterOnCountryAndCity=rst1     EndFunction 

Persisting Recordsets

By using the Save method, you can persist a recordset. Later, with the Open method and the MSPersist provider, you can open a saved recordset. This capability serves a couple of requirements particularly well. First, it comes in handy when you need to take a snapshot of a record source for viewing later on a computer disconnected from the data source for the records. Second, when remote server access is either slow or unreliable, using a saved recordset based on a record source from a remote server gives an application fast, reliable data access. This is especially convenient for read-only record sources. In either context, you can modify a saved record source and later update the remote source with the UpdateBatch method.

You can persist a recordset's contents with either of two formats, a proprietary format or an XML format. Designate your preferred format with a member of the PersistFormatEnum enum in the statement using the Save method. Saving a file in XML format enables another application to open and view the recordset's data. The Open method, which inserts the file's contents into a recordset, works equally well with either format. For example, you can readily view the saved XML file in the Microsoft Internet Explorer browser included with Office 2003. Internet Explorer allows you to view the XML tags and data. The proprietary format is not so easy to view natively. However, users can readily open a file in the proprietary format and then examine its contents in the recordset in which the Open method deposits the file's contents.

Note  

While the XML format used by the recordset's Save method is not fully compatible with the latest industry standard ( http://www.w3.org/TR/REC-xml ), it is nevertheless tagged text that you can read in a browser or Notepad. This readability feature makes XML an especially desirable format for sharing data with others.

The following sample opens a recordset on the Shippers table from the Northwind database on the C share of a remote computer named ccs1. Notice from the path specified in the Data Source parameter for the connection string argument that the original data source is an Access 2002 database file (you can readily open Access 2002 and Access 2000 database files from Access 2003). The procedure saves the recordset based on the connection to a drive on the local computer. The Save method designates an XML file format by using the adPersistXML intrinsic constant. Specifying the adPersistADTG intrinsic constant saves the recordset in the proprietary format.

 SubSaveAccessDBRecordSource() OnErrorGoToSaveAccessDB_Trap Dimcnn1AsADODB.Connection Dimrst1AsADODB.Recordset DimvarLocalAddressAsVariant     'Createtheconnection Setcnn1=NewADODB.Connection cnn1.Open"Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=\ccs1\c\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb;"     'OpenrecordsetonShipperstableinthecopyof 'Northwind.mdbonamachinenamedccs1 Setrst1=NewADODB.Recordset rst1.Open"Shippers",cnn1,adOpenKeyset,adLockOptimistic,adCmdTable     'SpecifyalocaladdressfortherecordsetbasedonShipperstable, 'andsavetherecordsetinXMLformattothataddress varLocalAddress="C:\Office11Files\Chapter01\ShippersFromCcs1.xml" rst1.SavevarLocalAddress,adPersistXML     SaveAccessDB_Exit: rst1.Close Setrst1=Nothing cnn1.Close Setcnn1=Nothing ExitSub     SaveAccessDB_Trap: Iferr.Number=58Then 'Killthetemporaryfileoftabledatabecauseitalreadyexists, 'andresumewritingtothefile KillvarLocalAddress Resume Else MsgBox"Procedurefailedwithanerrornumber="_ &err.Number&","&vbCrLf&"andan"&_ "errordescriptionof"""&_ err.Description&""""&".",vbInformation,_ "ProgrammingMicrosoftAccess2003" EndIf     EndSub 

The sample's error trap is slightly more robust than necessary, but you will typically need an error trap with a procedure that saves a recordset to a file. This is because you will generate a run-time error if you attempt to save a recordset over an existing file. Since the number for this specific error is 58, the sample kills the old version of the file and resumes executing the line of code with the Save method.

Figure 1-6 shows the saved file open in Internet Explorer. Notice that the XML tags show the schema information, such as data type and length for recordset fields, as well as the data for the file's contents.

click to expand
Figure 1.6: The XML contents for the file saved by the SaveAccessDBRecordSource procedure, as seen from Internet Explorer.

The OpenSavedRecordset sample that appears next illustrates the syntax for opening and browsing the contents of the file saved in the previous code sample. After declaring and instantiating a recordset, the procedure invokes the recordset's Open method. The source for the Open method is the path and filename for the recordset saved previously. In order to use the Open method this way, you must designate MSPersist as the provider for the connection argument. Notice that the Options argument is adCmdFile . I include this just to remind you of the general usefulness of specifying an Options argument. In this particular case, the argument is not necessary since ADO assumes an adCmdFile argument when you designate MSPersist as the provider.

After opening the file as a recordset, the code sample loops through the columns of each row as it prints the field name and value of each element in the recordset. With this simple code, you can open the local file and view its contents even when you are disconnected from the original version of the Shippers table on the ccs1 computer.

 SubOpenSavedRecordset() Dimrst1AsADODB.Recordset Dimint1AsInteger     'Instantiaterecordset,andopenfromfilewithPersistenceprovider Setrst1=NewADODB.Recordset rst1.Open_ "C:\Access11Files\Chapter01\ShippersFromCcs1.xml",_ "Provider=MSPersist;",_ ,,adCmdFile     'Loopthroughallrows;witheachrowloopthroughallcolumns DoUntilrst1.EOF Forint1=0Torst1.Fields.Count-1 Debug.Printrst1.Fields(int1).Name,rst1(int1) Nextint1 rst1.MoveNext Loop     'Cleanupobjects rst1.Close Setrst1=Nothing     EndSub 

As developers, we can use this technology to empower employees who must travel, enter data on a disconnected recordset, and then update the connected recorded source when they return to the office. By using the recordset's UpdateBatch method along with the ability to persist recordsets, you can readily meet this need. The SaveEditInsertShippersAtNorthwindCS procedure demonstrates the syntax that can help you empower "road warrior " employees. The sample creates a local recordset based on the Shippers table in the NorthwindCS database on the cab2000 SQL Server. (Recall that the NorthwindCS database ships with Microsoft Office.) Then the procedure saves this recordset to a local drive.

 SubSaveEditInsertShippersAtNorthwindCS() OnErrorGoToEditInsertCS_Trap Dimcnn1AsADODB.Connection Dimrst1AsADODB.Recordset DimvarLocalAddressAsVariant     'Localaddressforsavedrecordset varLocalAddress="C:\Access11Files\Chapter01\ShippersFromCab2000.xml"     'Instantiateandopenarecordset Setrst1=NewADODB.Recordset rst1.Open"SELECTShipperID,CompanyName,PhoneFROMShippers",_ "Provider=SQLOLEDB;DataSource=cab2000;UserId=sa;"&_ "Password=password;InitialCatalog=NorthwindCS;",_ adOpenDynamic,adLockOptimistic,adCmdText     'PersisttherecordsettodiskasanXMLfile;thissavesit 'basedontheShipperstableintheNorthwindCSdatabaseonthe 'cab2000server KillvarLocalAddress rst1.SavevarLocalAddress,adPersistXML     'OpentheXMLfilelocally Setrst1=NewADODB.Recordset rst1.OpenvarLocalAddress,"Provider=MSPersist;",,,adCmdFile     'Findarecord,andedititinthelocalcache rst1.Find"CompanyName='FederalShipping'" Ifrst1.EOFThen Debug.Print"Namenotfound." ExitSub EndIf rst1("CompanyName")="FederalShippers" rst1.Update     'Addarecordtothelocalcache rst1.AddNew rst1("CompanyName")="CABMovers" rst1("Phone")="(555)234.5678" rst1.Update     'Aftercommittingtheeditlocally,connecttotheremote 'datasourceandupdatetheremotesource Setcnn1=NewADODB.Connection cnn1.Open"Provider=SQLOLEDB;DataSource=cab2000;UserId=sa;"&_ "Password=password;InitialCatalog=NorthwindCS;" rst1.ActiveConnection=cnn1 rst1.UpdateBatch     EditInsertCS_Exit: 'Cleanupobjects rst1.Close Setrst1=Nothing cnn1.Close Setcnn1=Nothing ExitSub     EditInsertCS_Trap: Iferr.Number=53Then 'Triedtokillafilethatdoesn'texist,sosimplyresume ResumeNext ElseIferr.Number=58Then 'Killthetemporaryfileoftabledataifitalreadyexists, 'andresumewritingtothefile KillvarLocalAddress Resume Else MsgBox"Procedurefailedwithanerrornumber="_ &err.Number&","&vbCrLf&"andan"&_ "errordescriptionof"""&_ err.Description&""""&".",vbInformation,_ "ProgrammingMicrosoftAccess2003" EndIf     EndSub 

A traveling employee can use this file-based version of a recordset to edit existing data and enter new data. The code sample simulates these tasks programmatically. The application of the Update method commits the changes to the recordset's local cache, but another Save method can transfer those updates from memory to the disk-based version of the local recordset.

After returning from the field, employees will want to update the database at headquarters with entries made while on the road. An employee can accomplish this objective by running a program you prepare that opens the file-based version of the recordset, connects the recordset to the server at headquarters, and invokes the UpdateBatch method for the local recordset. The UpdateBatch method, along with the recordset Filter property, enables developers to reconcile conflicts when they occur. See the Access Help files for more detail on conflict-resolution issues. Another especially useful resource for advanced technical questions is the Search capability at the http://msdn.microsoft.com/office site. For example, a search for "UpdateBatch Filter" returns a topic titled "Detecting and Resolving Conflicts," which includes a code sample demonstrating the syntax for detecting and resolving conflicts with the UpdateBatch method.

Note  

If you run the same procedure as SaveEditInsertShippersAt-NorthwindCS , except for updating its connections so that it initially opens and later updates the Access Northwind database file, it will fail. The precise error description is "Cannot update 'ShipperID'; field not updateable." The solution to this problem for Access database files is to construct the initial file-based recordset without primary keys that have an AutoNumber data type. Use a SQL statement as your source for the saved recordset to accomplish this. This book's companion content includes a sample procedure, SaveEditInsertShippersAtNorthwind , with the detailed code to perform this task.

Sorting Recordsets

Many database applications benefit from a sorted recordset. Therefore, Microsoft added a Sort property to ADO recordsets. This is a very natural property for Access developers to use. Simply set the Sort property for a recordset to the list of field names on which you want the recordset sorted. Represent the field names as a comma-delimited string. The default sort order is ascending. However, you can explicitly set an ascending sort order for any field by trailing the field name with a blank followed by the ASC keyword. Replace the ASC keyword with DESC if you want a recordset sorted in descending order on the values within a field.

In order for Sort property assignments to operate , you must assign the adUseClient intrinsic constant to the recordset's CursorLocation property. ADO takes advantage of user-defined indexes for tables when sorting recordsets. When no user-defined indexes exist, ADO constructs a temporary index to help speed the sorting of recordset rows on a field. You can clear any special sort (and delete temporary indexes that are automatically constructed ) by assigning an empty string to the Sort property. You can also dynamically rearrange the order of rows within a recordset by designating a modified string of field names for the recordset's Sort property.

The following code sample demonstrates how to set the Sort property, while revealing the syntax for most of its typical uses. The sample begins by creating a recordset based on the Customers table. Notice that the sample assigns the adUseClient intrinsic constant to the recordset's CursorLocation property. It is essential for the CursorLocation property to have this setting (without this setting, the Sort property assignments fail at run time). Recall, however, that adUseClient is the default CursorLocation property. Therefore, unless you changed the default setting, you do not have to make the explicit assignment.

The procedure prints an excerpt of columns for the first five records in the currently sorted version of the recordset four different times. The first set of five records shows the default order, which is the order of the primary key or the order of entry for rows if no primary key exists. You don't need to set the Sort property to return records in this order (unless you are clearing a prior sort setting). The second set of five records sorts in ascending order on the City field values. To impose this order, the procedure merely assigns the string "City" to the Sort property. The next set of five records also sorts records on City field values, but in descending order. The Sort property setting for this order merely trails the field name by a space and the DESC keyword. The final sort order is by City field values within Country field values. Place the outer sort key, Country , first in the string for the Sort property so that the City field values appear in ascending order within each country.

 SubSortCustomersDefaultCityUpDown() Dimrst1AsADODB.Recordset Dimint1AsInteger Dimint2AsInteger Dimint3AsInteger Dimint4AsInteger     'Instantiateandopenrecordsetbasedoncustomers; 'setCursorLocationtoadUseClienttosupportsorting Setrst1=NewADODB.Recordset rst1.CursorLocation=adUseClient rst1.Open"Customers","Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice"&_ "\Office11\Samples\Northwind.mdb;"     'FindandsavemaximumlengthofCustomerID,CompanyName, 'andContactNamefields int2=FindTheLongest(rst1,"CustomerID")+1 rst1.MoveFirst int3=FindTheLongest(rst1,"CompanyName")+1 rst1.MoveFirst int4=FindTheLongest(rst1,"ContactName")+1     'Printrecordsindefaultorder rst1.MoveFirst Debug.Print"DefaultOrder"&vbCr Forint1=1To5 Debug.Printrst1("CustomerID")&_ String(int2-Len(rst1("CustomerID")),"")&_ rst1("CompanyName")&_ String(int3-Len(rst1("CompanyName")),"")&_ rst1("ContactName")&_ String(int4-Len(rst1("ContactName")),"")&_ rst1("Phone"),rst1("City"),rst1("Country") rst1.MoveNext Nextint1     'SortbyCityandprint rst1.Sort="City" rst1.MoveFirst Debug.PrintString(2,vbCr)&"OrderbyCityAscending"&vbCr Forint1=1To5 Debug.Printrst1("CustomerID")&_ String(int2-Len(rst1("CustomerID")),"")&_ rst1("CompanyName")&_ String(int3-Len(rst1("CompanyName")),"")&_ rst1("ContactName")&_ String(int4-Len(rst1("ContactName")),"")&_ rst1("Phone"),rst1("City"),rst1("Country") rst1.MoveNext Nextint1     'SortbydescendingCityorderandprint rst1.Sort="CityDESC" rst1.MoveFirst Debug.PrintString(2,vbCr)&"OrderbyCityDescending"&vbCr Forint1=1To5 Debug.Printrst1("CustomerID")&_ String(int2-Len(rst1("CustomerID")),"")&_ rst1("CompanyName")&_ String(int3-Len(rst1("CompanyName")),"")&_ rst1("ContactName")&_ String(int4-Len(rst1("ContactName")),"")&_ rst1("Phone"),rst1("City"),rst1("Country") rst1.MoveNext Nextint1     'SortbyCitywithinCountryandprint rst1.Sort="Country,City" rst1.MoveFirst Debug.PrintString(2,vbCr)&_ "OrderbyCountryAscending,CityAscending"&vbCr Forint1=1To5 Debug.Printrst1("CustomerID")&_ String(int2-Len(rst1("CustomerID")),"")&_ rst1("CompanyName")&_ String(int3-Len(rst1("CompanyName")),"")&_ rst1("ContactName")&_ String(int4-Len(rst1("ContactName")),"")&_ rst1("Phone"),rst1("City"),rst1("Country") rst1.MoveNext Nextint1     'Cleanupobjects rst1.Close Setrst1=Nothing     EndSub 

You can combine the Sort property for recordsets with other Access programming features to achieve useful results that are difficult to obtain programmatically any other way. For example, it is easy to list all the tables in an Access database file with any number of properties sorted however you need. The OpenSchema method for the Connection object returns recordsets with fields describing classes of objects within a database. This chapter demonstrated how to use this method earlier, but it did not show you how to customize the order of the output. This section demonstrates how to use the Save and Open methods in combination with the Sort property to arrange OpenSchema output to suit your needs.

When used with a parameter set to adSchemaTables , the OpenSchema method outputs a recordset of table properties with information ordered by table name. However, this list mixes information about all types of tables in rows adjacent to one another. Therefore, it is difficult to find information about a particular user-defined table, view, or system table. Figure 1-7 shows the output to the Immediate window from a VBA program that sorts tables by table name, within table type. Along with the other two columns, this feature makes it easy to look up when a table was last modified or created.

click to expand
Figure 1.7: Program output from the SortedTableTypes procedure, showing a list of tables sorted by table name within table type from the Northwind Access database file.

The following pair of procedures generate the output shown in Figure 1-7. While this listing is slightly long, you are already familiar with all the techniques that it integrates. The calling procedure, CallSortedTableTypes , designates a string that points at a database file and passes that string to the second procedure, SortedTableTypes . You can get a listing like the one in Figure 1-7 for any Access database file, just by changing the string.

The second procedure generates a recordset about the tables in a database file by invoking the OpenSchema method with the adSchemaTables intrinsic constant as an argument. You cannot sort the output from the OpenSchema method because it sets the CursorLocation property to adUseServer . Therefore, the procedure saves the recordset and opens it again. This two-step process creates a version of the recordset with a CursorLocation property equal to adUseClient . In addition, the process yields a local version of the recordset for future use (for example, when you are disconnected from the computer with the database file). After changing the CursorLocation property setting, the procedure just needs to set the Sort property and print the results to the Immediate window. By specifying TABLE_TYPE before TABLE_NAME , the procedure forces the output to show table names sorted alphabetically within the table type.

 SubCallSortedTableTypes() Dimstr1AsString     'Specifydatabaseforwhichtogenerateasortedlistoftables str1="Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice"&_ "\Office11\Samples\Northwind.mdb;"     'Callsubproceduretocreatesortedlistoftables SortedTableTypesstr1     EndSub     SubSortedTableTypes(str1AsString) OnErrorGoToSortedTableTypes_Trap Dimcnn1AsADODB.Connection Dimrst1AsADODB.Recordset Dimrst2AsADODB.Recordset Dimint1AsInteger Dimint2AsInteger     'Instantiateandopenconnection Setcnn1=NewADODB.Connection cnn1.Openstr1     'Savetablesreportinrst1 Setrst1=cnn1.OpenSchema(adSchemaTables)     'SinceOpenSchemareturnsaserver-basedcursor,saveoriginal 'returnedrecordsetandreopensaveddatawithanewrecordset; 'thenewrecordsethasalocalcursorthatyoucansort rst1.Save"C:\Access11Files\Chapter01\SortedTables.adtg",adPersistADTG Setrst2=NewADODB.Recordset rst2.Open"C:\Access11Files\Chapter01\SortedTables.adtg","Provider=MSPersist"     'Sortcopieddatabytablenamewithintabletype rst2.Sort="TABLE_TYPE,TABLE_NAME"     'ComputemaximumlengthofTABLE_NAMEandTABLE_TYPEcolumns int1=FindTheLongest(rst2,"TABLE_NAME")+1 rst2.MoveFirst int2=FindTheLongest(rst2,"TABLE_TYPE")+1 rst2.MoveFirst     'LoopthroughsortedresultssetfromOPENSCHEMAquery, 'andprintselectedcolumns DoUntilrst2.EOF Debug.Printrst2.Fields("TABLE_NAME")&_ String(int1-Len(rst2("TABLE_NAME")),"")&_ rst2.Fields("TABLE_TYPE")&_ String(int2-Len(rst2("TABLE_TYPE")),"")&_ rst2.Fields("DATE_MODIFIED"),_ rst2.Fields("DATE_CREATED") rst2.MoveNext Loop     SortedTableTypes_Exit: rst1.Close rst2.Close cnn1.Close Setrst1=Nothing Setrst2=Nothing Setcnn1=Nothing ExitSub     SortedTableTypes_Trap: Iferr.Number=58Then 'Killthetemporaryfileoftabledataifitalreadyexists, 'andresumewritingtothefile Kill"C:\Access11Files\Chapter01\SortedTables.adtg" Resume Else MsgBox"Procedurefailedwithanerrornumber="_ &err.Number&","&vbCrLf&"andan"&_ "errordescriptionof"""&_ err.Description&""""&".",vbInformation,_ "ProgrammingMicrosoftAccess2003" ResumeSortedTableTypes_Exit EndIf     EndSub 



Programming Microsoft Office Access 2003
Programming MicrosoftВ® Office Access 2003 (Core Reference) (Pro-Developer)
ISBN: 0735619425
EAN: 2147483647
Year: 2006
Pages: 144
Authors: Rick Dobson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net