ADO Recordset Object Properties and Collections
Let's take a look at each of the different properties available on the Recordset object and listed in the following table.
| Recordset Object Properties and Collections | ||
|---|---|---|
| Property or Collection Name | Data Type | Description |
| AbsolutePage | Long | Identifies the page the current record of the Recordset is on |
| AbsolutePosition | Long | Identifies the position of the current record in the Recordset |
| ActiveCommand | Variant | Pointer to the Command object that created the Recordset object |
| ActiveConnection | String or Connection | Specifies the Connection object used to retrieve the results of your query |
| BOF | Boolean | Indicates whether you're currently at beginning of file (BOF), the position before the first record in your Recordset |
| Bookmark | Variant | Allows you to return to a particular record in the Recordset |
| CacheSize | Long | Specifies the number of records that ADO will cache from the server |
| CursorLocation | Long | Specifies the location of the cursor service (either client-side or server-side) that will maintain the results of your query |
| CursorType | CursorTypeEnum | Specifies the type of cursor used to access the results of your query |
| DataMember | String | Controls which Recordset in the data source you're binding to |
| DataSource | Object | Allows you to bind the Recordset to a data source |
| EditMode | EditModeEnum | Specifies the editing status for the current record in the Recordset |
| EOF | Boolean | Indicates whether you're currently at end of file (EOF), the position following the last record in your Recordset |
| Fields | Collection of Field objects | Pointer to the collection of Field objects that contain the results of your query |
| Filter | Variant | Allows you to filter your Recordset for particular records |
| Index | String | Controls the index currently in effect for a Record-set object |
| LockType | LockTypeEnum | Specifies how the contents of your Recordset can be locked and updated |
| MarshalOptions | Long | Controls which records are included in your Recordset when it is passed across a process boundary |
| MaxRecords | Long | Specifies the maximum number of records to be returned by your query |
| PageCount | Long | Returns the number of pages in your Recordset |
| PageSize | Long | Specifies the number of records per page in your Recordset |
| Properties | Collection of Property objects | Collection of dynamic properties |
| RecordCount | Long | Returns the number of records in the Recordset |
| Sort | String | Allows you to reorder the data in your Recordset |
| Source | String or Command | Contains the query string used to build the Recordset |
| State | Long | Specifies the current state of the Recordset |
| Status | RecordStatusEnum | Returns the update status of the current record |
| StayInSync | Boolean | Controls whether references to child Recordsets will be kept in sync as you navigate through the parent Recordset |
AbsolutePage, PageCount, and PageSize Properties
The PageSize property specifies the number of records per page in your Recordset. If you have a Recordset with 81 records and the PageSize property is set to 10 (its default value), the Recordset will have nine pages—the first eight pages containing 10 records each and the last page containing only one record. You can change the value of the PageSize property at any time. If the Recordset is currently open, the PageCount and AbsolutePage properties will be updated automatically.
The PageCount property is read-only and reflects the number of pages in the Recordset. If the Recordset does not support pages or bookmarks, this property will return -1.
The AbsolutePage property returns a long value. AbsolutePage will return the page number of the current record and is 1-based. You can use this property only if it's supported by the provider and if your record pointer is currently at a valid record. If your record pointer is not pointing to a valid record, AbsolutePage will contain a value in the form of a PositionEnum value constant. (See the following table.) If your record pointer is currently at BOF or EOF, the AbsolutePage property will return adPosBOF or adPosEOF, respectively. AbsolutePage will return adPosUnknown if the Recordset's current record is neither BOF nor EOF and the Recordset doesn't support bookmarks, or if the Recordset is empty.
If the Recordset supports bookmarks, you can set the AbsolutePage property to a long value, between 1 and PageCount, while the Recordset is open. You cannot set the AbsolutePage property to adPosBOF or adPosEOF. If you set the AbsolutePage property, ADO will move the current record pointer to the first record on that page.
| PositionEnum Values | ||
|---|---|---|
| Constant | Value | Description |
| adPosUnknown | -1 | The Recordset contains no data, the current position is unknown, or the property isn't supported for the current CursorType or provider. |
| adPosBOF | -2 | The Recordset is at BOF, just before the first record. |
| adPosEOF | -3 | The Recordset is at EOF, just after the last record. |
As of ADO 2.1, the AbsolutePage property is supported for client-side Recordsets for all providers, and for server-side static and keyset Recordsets by means of the Microsoft OLE DB Provider For SQL Server and the Microsoft Jet 4.0 OLE DB Provider.
You can determine whether the Recordsets for your choice of OLE DB provider, cursor location, and cursor type will support the AbsolutePage property by using the Recordset.Supports method with the adApproxPosition constant.
AbsolutePosition Property
The AbsolutePosition property is similar to the AbsolutePage property, except that it refers to the record number within the Recordset rather than the page number. If your Recordset is at BOF or EOF, AbsolutePosition will return adPosBOF or adPosEOF, respectively. If the provider supports this property and your current record pointer is pointing to a record in the Recordset (neither BOF nor EOF), AbsolutePosition will return a number between 1 and the number of records in the Recordset.
For a Recordset that contains data and supports bookmarks, you can set this property to any number between 1 and the number of records in the Recordset to move to that specific record. You cannot set this property to adPosBOF or adPosEOF to move to BOF or EOF, respectively.
As of ADO 2.1, this property is supported for client-side Recordsets for all providers, and for server-side static and keyset Recordsets by means of the OLE DB Provider For SQL Server and the Jet 4.0 OLE DB Provider.
ActiveCommand Property
The ActiveCommand property contains the Command object that created the Recordset object. This property is read-only and is available even after the Recordset object has been closed.
ActiveConnection Property
For an open Recordset object, the ActiveConnection property is set to the Connection object used to retrieve your data. The property will not change once the Recordset object is closed. If your Recordset object was opened from a file, returned from another process, or created by populating the Fields collection yourself, this property will return Nothing, the default.
You can set the ActiveConnection property either to a connection string or to a Connection object while the Recordset is closed. You can also modify this property while the Recordset object is open, but only if you're using a client-side Recordset.
If you assign a connection string to the ActiveConnection property, ADO will create a new Connection object and attempt to connect to your database based on this string.
If you're using a client-side Recordset, you can set the ActiveConnection property to Nothing after opening the Recordset in order to disconnect the Recordset from your database. Once you've done that, the Recordset object will still contain the results of your query even after you close the Connection object.
BOF and EOF Properties
When you open a Recordset, you might want to programmatically loop through the records until you've reached the end of the Recordset. The BOF and EOF properties allow you to do this by letting you know when you've reached the beginning or end of the Recordset. Before the first true record in the Recordset, there is a placeholder that designates the beginning of the Recordset. Similarly, another placeholder designates the position following the last record in the Recordset. For example, you could use the following code to loop through the results of your query:
Dim strSQL As String Dim rsCustomers As ADODB.Recordset |
'Specify the query string. strSQL = "SELECT * FROM Customers" 'Open a Recordset. rsCustomers.Open strSQL, cnDatabase, adOpenStatic 'Check to see whether we've passed the last record in the Recordset. 'If we haven't, call DisplayCustomer and then move to the next record. Do While Not rsCustomers.EOF DisplayCustomer rsCustomers.MoveNext Loop After you display the contents of the last record in the Recordset by means of the DisplayCustomer routine, the next call to the MoveNext method will move the current record pointer in the Recordset beyond the last record to EOF. At that point, the EOF property will be equal to True and you will exit the loop. If no records satisfy the results of your query, both BOF and EOF will be set to True.
Bookmark Property
Just as you use a bookmark to mark your place in a book, the Bookmark property allows you to mark a particular record in your Recordset. You can retrieve a bookmark for a particular record in a Recordset by using the Bookmark property. Setting the Bookmark property will set the current record pointer to the record at that bookmark.
Different OLE DB providers implement bookmarks in different ways. The actual value of the Bookmark property is unimportant. The property was not designed for you to keep track of the record number; it was designed so that you can access the contents of a particular record based on the value of the Bookmark property. The purpose of the property is to allow you to return to a particular record in the Recordset.
If you regenerate a Recordset by closing and opening it or by calling the Requery method, a new set of bookmarks is created—one for each record. As a result, a bookmark you retrieved before regenerating your Recordset might not point to the same record after you regenerate the Recordset. You might receive an error if you attempt to set the Bookmark property to a value retrieved prior to regenerating the Recordset. Even if you set the property without generating an error, you might find that you're looking at a different record than expected. The same is true if you pick up another edition of a particular book—there's no guarantee that the contents of page 45 will be the same in both editions.
The code that follows uses the Find method to try to locate a particular customer in the Recordset. If the search fails, we use the Bookmark property to return to the record that was current prior to the search.
Dim varBookmark As Variant Dim rsCustomers As ADODB.Recordset |
CAUTION
Do not set the Bookmark property to a value in BookmarkEnum. Those constants are designed for the Find method, which we'll discuss a little later in this chapter.
CacheSize Property
If you're using a server-side Recordset, the CacheSize property specifies how many records ADO will retrieve from the OLE DB provider and store in its local cache. As you navigate through the Recordset, ADO will clear the cache and retrieve a new set of records from the OLE DB provider any time you navigate outside the currently cached records in the Recordset. This property defaults to 1, but setting this property to larger values might help decrease network activity. The CacheSize property can be modified even after the Recordset has been opened, but the change will not take effect until you move beyond the currently cached records.
CursorLocation Property
This property determines how the results of your query will be stored. You can set it to a value defined by CursorLocationEnum and shown in this table.
| CursorLocationEnum Values | ||
|---|---|---|
| Constant | Value | Description |
| adUseServer | 2 | Default; uses the OLE DB provider and/or database to manage the results of your queries |
| adUseClient | 3 | Uses the ADO Cursor Engine to manage the results of your queries |
| adUseClientBatch | 3 | Hidden; equivalent of adUseClient |
| adUseNone | 1 | Hidden, undocumented, and unsupported (That warning should suffice.) |
The default setting (adUseServer) uses server-side Recordsets, but you can set it to use client-side Recordsets by changing the setting to adUseClient. We'll talk more about the differences between server-side and client-side Recordsets in the coming chapters. Until that time, you can get by if you understand that setting CursorLocation to adUseClient causes the results of your entire query to be stored in the ADO Cursor Engine. Server-side Recordsets are managed by either the OLE DB provider or the database (depending on your database), with ADO storing a small portion of the data itself.
There are two hidden values in CursorLocationEnum, and the less said about these properties the better. Generally, hidden features are hidden for a reason—this is an object model, not a video game with hidden cheat codes and power-up features. The first value is adUseClientBatch. It exists for backward compatibility, and its use is identical to that of adUseClient. I am under strict orders not to talk about the last value, adUseNone. I asked about this value on an internal e-mail alias once and was sentenced to my cubicle for two weeks without caffeine. While this value looks like rdUseNone in Remote Data Objects (RDO), it isn't the same and is hidden for a reason to which I'm not privy.
Unless you set the Recordset's CursorLocation property, the Recordset will inherit the value of the Connection object's CursorLocation property if you used a Connection object to open your Recordset. If you do set the Recordset's CursorLocation property prior to opening the Recordset, ADO will use the value specified in the Recordset's CursorLocation property to determine the location of the results of your query. This property is read/write while the Recordset object is closed and read-only while the Recordset is open.
CursorType Property
The CursorType property contains a value that indicates the type of cursor used to store the contents of the Recordset. You can use this property to request a particular cursor type before you open the Recordset. The available values for the CursorType property appear in the following table.
| CursorTypeEnum Values | ||
|---|---|---|
| Constant | Value | Description |
| adOpenForwardOnly | 0 | Default for server-side Recordsets; opens a Recordset that supports scrolling forward only |
| adOpenStatic | 3 | Default and only possible value for client-side Recordsets; supports scrolling forward and backward; changes made by other users are not visible |
| adOpenKeyset | 1 | Supports scrolling forward and backward; modifications and deletions by other users are visible |
| adOpenDynamic | 2 | Supports scrolling forward and backward; modifications, deletions, and insertions made by other users are visible |
If you set the CursorType property to a value that the cursor location or provider doesn't support, you'll receive the closest available cursor type. The CursorType property is read/write while the Recordset is closed and read-only while the Recordset is open. You can set the CursorType property prior to calling the Open method, or you can set the CursorType parameter on the Open method, which will have the same effect.
Chapter 7 will go into much greater depth about cursors, their behavior, and their availability.
DataMember and DataSource Properties
Much like the Microsoft Visual Basic DataGrid control, the Recordset object can act like a complex data-bound control. You can set the DataSource property of the Recordset object to a data source such as the Visual Basic DataEnvironment or the Microsoft ADO Data Control, or to another Recordset object. If the data source you're binding the Recordset object to exposes multiple Recordsets, you can use the DataMember property to specify the Recordset to which you want to bind:
Set rsDataBound = New ADODB.Recordset rsDataBound.DataMember = "Command1" rsDataBound.DataSource = DataEnvironment1 |
EditMode Property
The EditMode property is read-only and indicates the editing status for the current record. EditMode will return one of the values in EditModeEnum, shown in the following table.
| EditModeEnum Values | ||
|---|---|---|
| Constant | Value | Description |
| adEditNone | 0 | Default; the record is not currently being modified. |
| adEditInProgress | 1 | The current record is being modified. |
| adEditAdd | 2 | The current record has been added to the Recordset with the AddNew method, but the Update method hasn't been called to commit the changes to the Recordset. |
| adEditDelete | 4 | The current record has been deleted. |
If you're not editing the current record in the Recordset, the EditMode property will be set to adEditNone. If you modify one of the fields in the record or if you're in the process of adding a record to the Recordset, the EditMode property will return adEditInProgress until you call either the Update or the CancelUpdate method.
Calling the Delete method on the Recordset object will delete the current record from the Recordset but will not move to the next valid record. Until you move from the deleted record, EditMode will return adEditDelete.
Fields Collection
The Recordset object contains a Fields collection. Each object in this collection refers to a field in the results of your query. The Fields collection is the default property for the Recordset object, and the Value property is the default property for the Field object. There are many different ways to refer to a Field object in the Recordset's Fields collection. The Fields collection is 0-based. In the following Visual Basic code, all lines are equivalent to each other, referring to the Value property for the CustomerID field in the rsCustomers Recordset object (assuming CustomerID is the first field in the Recordset):
rsCustomers.Fields("CustomerID").Value rsCustomers.Fields(0).Value rsCustomers(0).Value rsCustomers("CustomerID").Value rsCustomers!CustomerID.Value rsCustomers.Fields("CustomerID") rsCustomers.Fields(0) rsCustomers(0) rsCustomers("CustomerID") rsCustomers!CustomerID |
We'll discuss the Fields collection and the Field object later in this chapter, in the section "ADO Fields Collection."
Filter Property
The Filter property allows you to specify which records in your Recordset you want to view. This feature is designed for client-side Recordsets, but in most cases it also works with server-side Recordsets; just keep in mind that the ADO Cursor Engine is providing this functionality. Thus, if you're using a server-side Recordset, you're still retrieving all records that satisfy your query, but ADO is hiding the records that don't satisfy the criteria of the setting for the Filter property.
You can set the Filter property to a string to specify the filter criteria, to an array of bookmarks, or to any of the values in FilterGroupEnum (shown in the following table).
| FilterGroupEnum Values | ||
|---|---|---|
| Constant | Value | Description |
| adFilterNone | 0 | Default; clears the current filter |
| adFilterPendingRecords | 1 | Displays only the records with pending changes |
| adFilterAffectedRecords | 2 | Displays only the records affected by the last call to Delete, Resync, UpdateBatch, or CancelBatch |
| adFilterFetchedRecords | 3 | Displays only the records currently stored in the cache |
| adFilterPredicate | 4 | Hidden, undocumented, and unsupported constant |
| adFilterConflictingRecords | 5 | Displays only the records that failed in the last batch update attempt |
FilterGroupEnum constants
Here's a more detailed explanation of what the different constants in FilterGroupEnum are designed to do:
- adFilterNone This constant clears the current filter and makes all records in the Recordset visible. Setting the Filter property to an empty string has the same effect, because even if you set the Filter property to an empty string, ADO will actually set the property to adFilterNone. This means that you can reliably use the Filter property to see whether there is an active filter by testing for a value of adFilterNone.
- adFilterPendingRecords This filter value can come in handy if you're using the ADO Cursor Engine's batch updating functionality. When you set the Filter property to adFilterPendingRecords, only the records with pending changes will be visible in the Recordset. This includes records that you have modified, inserted, or deleted but whose changes have not yet been committed to the database. You can walk through the records visible in the Recordset object and check the Status property to determine how the record has been changed: by modification, deletion, or insertion. We'll talk more about this feature when we discuss using disconnected Recordsets and batch updates.
- adFilterAffectedRecords Setting the Filter property to this value displays only the records modified by the last call to the Delete, Resync, UpdateBatch, or CancelBatch method. To be honest, I've yet to find a scenario in which this filter value would be useful, but anything is possible.
- adFilterFetchedRecords This is another odd value. It's designed for server-side Recordsets and is related to the Recordset's CacheSize property. By setting Filter to adFilterFetchedRecords on a server-side Recordset, you'll see only the records currently stored in the cache. The advantage to this is that you can then navigate through the Recordset without invoking a network round-trip.
- adFilterPredicate At the time of this writing, this constant is hidden, undocumented, and unsupported. Perhaps it will become a viable feature in a future release of ADO. My attempts to use this constant generated an invalid page fault.
Using this filter value with a client-side Recordset works a bit differently. Since all the data in a client-side Recordset is stored in the ADO Cursor Engine, there really isn't any cached data. Nevertheless, if you set the Filter property on the Recordset to this constant, only the number of records specified in the CacheSize property, counting from the current record, will be visible. If the number of records remaining, including the current record, is less than the value of the CacheSize property, only those remaining records will be visible.
NOTE
I have absolutely no idea why this constant exists. Don't trust anyone who claims to know what it's for, unless they can show you sample code that works.
- adFilterConflictingRecords This constant hides all records in the Recordset except those that failed in the last batch update attempt. If you want to modify a record in your database but another user has modified that record between the time you retrieved it and the time you called UpdateBatch, ADO will mark that record as a conflict. Setting the Filter property on the Recordset to adFilterConflictingRecords will display only the records marked as conflicts in your last call to UpdateBatch. We'll talk more about this feature when we discuss using disconnected Recordsets and batch updates.
Throughout the rest of this text, I'll ignore this constant when discussing values from FilterGroupEnum, as should you.
Setting the Filter property to a string
You can also set the Filter property of the Recordset to a string in order to specify which records you want to view. For example, if you retrieved the entire contents of your Orders table and wanted to view the orders only for a particular customer, you could use code such as the following:
rsOrders.Filter = "CustomerID = 'ALFKI'" |
While ADO's filtering capabilities aren't as robust as those for the SQL Server and Jet database engines, they're still helpful. Bear in mind the following rules when setting the Filter property to a string:
- Each clause must use a field name in the Recordset, an operator (=, <>, <, >, <=, >=, or LIKE), and a value.
- If you use the LIKE operator to compare strings, you can use a wildcard (% or *) as the last character of the search string or as the first and last characters in the string.
- You can use multiple criteria, separated by AND or OR clauses. There is no precedence between AND and OR clauses. You can group clauses with parentheses, but you can only join groups of clauses with OR.
- If the field name you're referencing contains a space, you can delimit it with square brackets:
- You can delimit date values with single quotes or pound symbols, but it's not required unless you're specifying a date and a time. I'd still recommend using one of the delimiters.
- You must delimit strings with either a single quote or a pound symbol. If there's a single quote in the string you're searching for, pound symbols might seem like the only choice. But here's a trick: you can delimit the string with single quotes if you change the single quotes in the embedded string to two consecutive single quotes. (Readers with some SQL Server experience might recognize this stratagem.) You don't need to do this if you delimit the string with pound symbols. However, if you need to use a pound symbol in the string you're searching for, you can't use two consecutive pound symbols. For example, if you were looking for the customer record with a CompanyName field value of Trail's Head Gourmet, you could use the following code:
rsAuthors.Filter = "[Year Born] = 1945" |
strCriteria = "CompanyName = 'Trail''s Head Gourmet'" rsCustomers.Filter = strCriteria |
If you're prompting the user for the value to search for, do that person a favor and hide this level of complexity. After you receive the user's input, search for single quotes and replace them with two consecutive single quotes. Visual Basic 6 added a Replace function that makes this process simple:
strCriteria = "CompanyName = '" & _ Replace(strCompanyName, "'", "''") & "'" rsCustomers.Filter = strCriteria |
Setting the Filter property to an array of bookmarks
You can set the Recordset's Filter property to an array of bookmark values. The following code retrieves the bookmarks from two records of the Recordset, then displays the two records that were active when the Recordset's Bookmark property was examined:
Dim aBookmarks(1) As Variant aBookmarks(0) = rsCustomers.Bookmark rsCustomers.MoveNext aBookmarks(1) = rsCustomers.Bookmark |
Visual Basic grids and filters
At the time of this writing, the DataGrid and HierarchicalFlexGrid controls that ship with Visual Basic 6 do not handle non-string-based filters very well. If you set the Filter property on a Recordset to an array of bookmarks or to a value in FilterGroupEnum other than adFilterNone, the DataGrid control will still display all the records in the Recordset. The grid controls are also unable to display server-side Recordsets using string-based filters.
Hopefully, the complex data-bound controls (such as grids, list boxes, and combo boxes) that ship with the next version of Visual Basic will handle filtered Recordsets better.
Index Property
The basic idea behind the Index property is that you can use a table's index to control the order in which records in a table are displayed. You can then use the Recordset's Seek method, which we'll discuss later in this chapter, to search for a record based on its index value or values.
At the time of this writing, the only provider that supports the Index property is the Jet 4.0 OLE DB Provider, but only if you're using a Jet 4 and Access 2000-format database with a Recordset that has a CommandType setting of adCmdTableDirect. To determine whether your Recordset supports indexes, use the Supports method of the Recordset object with the adIndex constant.
If you're using a Recordset object that supports the Index property, you can set the Index property to a string that corresponds to the name of one of the indexes for the table that you've queried. You can set the Index property on a closed or open Recordset. If you set the Index property on an open Recordset that does not support indexes, you'll receive a trappable error.
Keep in mind that if you set the Index property on a closed Recordset, the Index property is supported only in a very specific scenario. From personal experience, I've found that you can set the Index property on a closed Recordset and then open the Recordset in a way that does not support indexes, without generating an error—which implies success. It appears that if you set the Index property prior to opening your Recordset, you'll receive an error only if the Recordset does support indexes and the specified index does not exist, or if you don't open your Recordset with the CommandType parameter on the Open method set to adCmdTableDirect.
The Index property is designed to provide functionality similar to the Index property on a Data Access Objects (DAO) Recordset object. The fact that it's currently supported for use only with Access databases is not a coincidence. It's a conscious attempt by the ADO development team to provide ADO with as much of DAO's functionality as possible. The scenario under which ADO's Index property is supported, as just described in the previous paragraph, is similar to the scenario in which DAO Recordsets support the Index property—using table-type recordsets. (See the DAO documentation for information on table-type recordsets.)
LockType Property
The Recordset's LockType property dictates whether and how your Recordset can be updated. This property is read/write when the Recordset is closed and read-only when the Recordset is open. You can also set this property by specifying the LockType parameter on the Recordset's Open method. You can assign any of the LockTypeEnum values shown in the following table to the LockType property.
| LockTypeEnum Values | ||
|---|---|---|
| Constant | Value | Description |
| adLockReadOnly | 1 | Default; the Recordset is read-only. |
| adLockPessimistic | 2 | ADO relies on the OLE DB provider to ensure that your update attempt will succeed. |
| adLockOptimistic | 3 | The data is not locked until you call Update. |
| adLockBatchOptimistic | 4 | Modifications to your data are cached until you call UpdateBatch. |
Locking options
The following is a brief explanation of the different locking options available to you in ADO. We'll discuss updating your database with ADO in more depth in Chapters 8, 10, and 12.
- adLockReadOnly Read-only Recordsets need little explanation. If you know you're not going to update the data you're retrieving, request read-only Recordsets. You'll get your data back faster.
- adLockPessimistic The idea behind pessimistically locked Recordsets is fairly simple: make sure that attempts to update succeed. This means that attempts to edit will fail in order to prevent multiple users from simultaneously trying to edit the same record. Such attempts are considered "pessimistic" because you're expecting that multiple users will try to update the same data at the same time.
NOTE
Pessimistic locking is falling further and further out of favor. In upcoming chapters, we'll take a look at why and then focus on optimistic locking.
- adLockOptimistic While optimistically locked Recordsets might require more code, they are much more appropriate than pessimistic locks for multiuser applications. Two users can simultaneously edit the same record of data. Whoever commits their changes first will successfully update that record, and the second user's attempt to update that data will fail. There's nothing wrong with a failed attempt to update data; you just have to handle such failures elegantly.
- adLockBatchOptimisticBatch optimistic updates are more optimistic than normal optimistic updates. In batch updates, ADO caches the updates until you call the UpdateBatch method on the Recordset object. This feature is primarily designed for client-side Recordsets, but it can also work with server-side Recordsets if the OLE DB provider and/or database supports having multiple records with pending changes. For example, SQL Server cursors support this functionality, while Microsoft Access cursors do not.
If you request a LockType that is not supported for the combination of provider, cursor location, and cursor type, you'll receive the closest available LockType.
MarshalOptions Property
In Chapter 15, we'll talk about passing a Recordset object across process boundaries. You can pass a Recordset object from a business object to a client application across a process boundary. The MarshalOptions property, which is read/write at all times, is designed to improve performance in this type of multitiered application using ADO's batch updating feature. If the client application modifies data and you want to pass that data from the Recordset back to the business object to update the database, passing the unmodified records across the process boundary is likely a waste of time and bandwidth. You can designate whether all records will be passed, or only those that have been updated, by setting the MarshalOptions property to one of the MarshalOptionsEnum values shown in the following table.
| MarshalOptionsEnum Values | ||
|---|---|---|
| Constant | Value | Description |
| adMarshalAll | 0 | Default; ADO will pass all records in the Recordset across process boundaries. |
| adMarshalModifiedOnly | 1 | ADO will include only records that contain pending changes when the Recordset is passed across process boundaries. |
MaxRecords Property
How much data will your queries retrieve? Are you building your own queries, or does your application build queries based on interaction with the user? How do you know that the query you're about to execute won't return thousands upon thousands of records?
The MaxRecords property is designed to limit the number of records returned by a query. By default this property is set to 0, which means that ADO won't place any restrictions on the number of records returned by the query. You can set this property to a long integer prior to opening the Recordset object to limit the number of records returned by your query:
strSQL = "SELECT * FROM Customers" Set rsCustomers = New ADODB.Recordset rsCustomers.MaxRecords = 100 rsCustomers.Open strSQL, cnDatabase, adOpenStatic |
ADO doesn't actually implement this feature, however. Instead, ADO passes this information along to the OLE DB provider, and it's up to the provider to support this functionality. At the time of ADO 2.5's release, this feature was implemented for the OLE DB Provider For SQL Server and the SQL Server ODBC driver, as well as for the OLE DB Provider For Oracle and the ODBC driver for Oracle.
If you're using an Access database, you'll find that the MaxRecords property does not affect the number of records your query returns. The OLE DB provider and ODBC driver for Access databases support similar functionality by allowing you to change your query string to include this information in the query itself:
strSQL = "SELECT TOP 10 * FROM Customers" Set rsCustomers = New ADODB.Recordset rsCustomers.Open strSQL, cnDatabase, adOpenStatic |
Properties Collection
Like the Connection object, the Recordset object exposes a Properties collection. When you assign the Recordset a valid connection to your database—by either opening the Recordset or setting the ActiveConnection property—ADO populates the Properties collection with provider-specific properties.
ADO also adds entries to the Properties collection that are specific to the ADO Cursor Engine if the Connection object the Recordset is using has its CursorLocation property set to adUseClient, or if the Recordset's own CursorLocation property is set to adUseClient.
If you'd like to learn more about what properties are exposed in the Properties collection for your database connection, examine the Properties collection and check the documentation in the Data Access SDK and/or the documentation provided for your specific OLE DB provider. We'll discuss many of the dynamic properties specific to the ADO Cursor Engine in later chapters of this book.
RecordCount Property
You can use the RecordCount property to determine how many records exist in your Recordset. RecordCount will return -1 if the provider or cursor doesn't support bookmarks. You can also use the Supports method of the Recordset object with the adBookmarks constant to determine whether your provider supports this feature so that your Recordset can return a valid value for RecordCount. Checking the value of this property while the Recordset is closed will generate a run-time error.
In general, you can obtain a valid value from the RecordCount property if you're using a Recordset based on a static or keyset cursor. We'll discuss cursors in greater depth in Chapter 7.
Sort Property
You can use the Sort property to control the order of records in your Recordset. This feature is implemented by the ADO Cursor Engine and works with only client-side Recordsets. Set the Sort property to a single field name or multiple field names separated by commas; if the field name contains a space, delimit it with square brackets. You can control whether ADO sorts in ascending or descending order for a particular field by using the ASC or DESC keywords after the field name in the sort string, as shown in the following code. By default, ADO sorts in ascending order.
strSQL = "SELECT * FROM Authors" rsAuthors.CursorLocation = adUseClient rsAuthors.Open strSQL, cnDatabase, adOpenStatic rsAuthors.Sort = "[Year Born], Au_ID DESC" |
To unsort your Recordset, set the Sort property to an empty string.
When you sort on a field for the first time, the ADO Cursor Engine automatically builds a temporary index. This temporary index is deleted when you clear the Sort property. You can build the temporary index yourself before using the Sort property by setting the dynamic Optimize property on the desired Field object to True:
rsAuthors("Year Born").Properties("Optimize") = True |
Source Property
The Source property contains information about the query used to build your Recordset. You can set the Source property to a string or a Command object when the Recordset is closed, but the Source property will return only a string value. If you use a CommandType property value other than adCmdText to submit your query, the Source property will contain the actual query string submitted to the provider. For example, if you specify only the table name in the query and use the adCmdTable constant for CommandType, the Source property will return the string "select * from TableName". If you use a parameterized query, the Source property will contain the parameter markers but not the values for those parameters submitted in the query.
State Property
You can check the value of the State property to determine the current state of your Recordset object. There are four possible values for the State property on a Recordset object, defined by the constants in ObjectStateEnum shown in this next table. The first two values are relatively straightforward: when the Recordset object is closed the State property is set to adStateClosed, and when it's open the State property is set to adStateOpen.
| ObjectStateEnum Values | ||
|---|---|---|
| Constant | Value | Description |
| adStateClosed | 0 | The Recordset object is closed. |
| adStateOpen | 1 | The Recordset object is open. |
| adStateConnecting | 2 | Not applicable to the Recordset object. |
| adStateExecuting | 4 | The Recordset object is executing your query. |
| adStateFetching | 8 | The Recordset object is fetching the results of your query. |
The other two values of the State property applicable to the Recordset object come into play when you're using ADO's asynchronous query features. If you execute your query asynchronously, the Recordset's State property will be set to adStateExecuting. The ADO Cursor Engine also supports fetching the results of your query asynchronously. If you use this feature, the State property will be set to 9 (adStateOpen + adStateFetching) while ADO continues to fetch data.
Status Property
The Status property on the Recordset object describes the status of the current record and is particularly useful when you're working with ADO's batch updating feature. You can use the Status property to examine records that contain pending changes and determine whether the records have been changed by insertion, deletion, or modification. Some developers use this information to modify the database on their own by means of a stored procedure or an action query (INSERT, DELETE, or UPDATE). The Status property can contain a combination of the values in RecordStatusEnum, shown in the following table.
| RecordStatusEnum Values | ||
|---|---|---|
| Constant | Value | Description |
| adRecOK | 0 | The record has not been modified or was successfully updated. |
| adRecNew | 1 | The record corresponds to a new pending record that has not been submitted to the database. |
| adRecModified | 2 | The record has been modified. This value is not used for deleted or inserted records, only for existing records that have been modified. |
| adRecDeleted | 4 | The record has been deleted from the Recordset. |
| adRecUnmodified | 8 | There are no pending changes for this record. |
| adRecInvalid | 16 | The record was not saved, because its bookmark is invalid. |
| adRecMultipleChanges | 64 | The record was not saved, because it would have affected multiple records. |
| adRecPendingChanges | 128 | The record was not saved, because it refers to a pending insert. |
| adRecCanceled | 256 | The record was not saved, because the operation was canceled. |
| adRecCantRelease | 1024 | The record was not saved, because of existing locks. |
| adRecConcurrencyViolation | 2048 | The record was not saved, because optimistic concurrency was in use. |
| adRecIntegrityViolation | 4096 | The record was not saved, because the user violated integrity constraints. |
| adRecMaxChangesExceeded | 8192 | The record was not saved, because there were too many pending changes. |
| adRecObjectOpen | 16384 | The record was not saved, because of a conflict with an open storage object. |
| adRecOutOfMemory | 32768 | The record was not saved, because the computer has run out of memory. |
| adRecPermissionDenied | 65536 | The record was not saved, because the user has insufficient permissions. |
| adRecSchemaViolation | 131072 | The record was not saved, because it violates the structure of the underlying database. |
| adRecDBDeleted | 262144 | The record has been deleted from the data source. |
You'll also want to use the Status property to determine why conflicts occurred when you called the UpdateBatch method on your Recordset. Since ADO relies on the OLE DB provider to determine the cause of the conflict, you might not receive the identical value for the Status property when the same problem occurs with different databases. Your best bet is to create the expected conflicts yourself and observe the results. We'll cover batch updating and conflicts in more detail in Chapter 12.
Keep in mind that this property is a bitmask and that you should use bitwise comparisons. In Visual Basic, you'd do this with the AND operator rather than the = operator.
I've been unable to come up with scenarios that will cause ADO to set this property to one of the more exotic constants, such as adRecObjectOpen, for the Status property.
StayInSync Property
The StayInSync property helps control the behavior of your hierarchical Recordset as you navigate through it. This property is set to True by default, which means that as you navigate through a Recordset, references to child Recordsets are automatically kept in synchronization.
Say you have a customers/orders hierarchical query, and you open the customers-level Recordset. If the customers-level Recordset's StayInSync property is set to True when you reference the orders-level Recordset, the data in the orders-level Recordset will remain synchronized with the current record in the customers-level Recordset as you navigate through the latter. If the StayInSync property on the customers-level Recordset is set to False when you reference the orders-level Recordset, the contents of that reference won't change as you navigate through the customers-level Recordset.
Changing the value of the StayInSync property of a Recordset object will have no effect on references you've already made to child Recordsets.
EAN: N/A
Pages: 131