Microsoft Cursor Service for OLE DB
The Microsoft Cursor Service for OLE DB supplements the cursor support functions of data providers. As a result, the user perceives relatively uniform functionality from all data providers.
The Cursor Service makes dynamic properties available and enhances the behavior of certain methods. For example, the Optimize dynamic property enables the creation of temporary indexes to facilitate certain operations, such as the Find method.
The Cursor Service enables support for batch updating in all cases. It also simulates more capable cursor types, such as dynamic cursors, when a data provider can only supply less capable cursors, such as static cursors.
Keyword
To invoke this service component, set the Recordset or Connection object's CursorLocation property to adUseClient.
connection.CursorLocation=adUseClient
recordset.CursorLocation=adUseClient
Dynamic Properties
When the Cursor Service for OLE DB is invoked, the following dynamic properties are added to the Recordset object's Properties collection. The full list of Connection and Recordset object dynamic properties is listed in the ADO Dynamic Property Index. The associated OLE DB property names, where appropriate, are included in parenthesis after the ADO property name.
Changes to some dynamic properties are not visible to the underlying datasource after the Cursor Service has been invoked. For example, setting the Command Time out property on a Recordset will not be visible to the underlying data provider.
…
Recordset1.CursorLocation = adUseClient 'invokes cursor service
Recordset1.Open "authors", _
"Provider=SQLOLEDB;Data Source=DBServer;User Id=usr;" & _
"Password=pwd;Initial Catalog=pubs;",,adCmdTable
Recordset1.Properties.Item("Command Time out") = 50
' 'Command Time out' property on DBServer is still default (30).
…
If your application requires the Cursor Service, but you need to set dynamic properties on the underlying provider, set the properties before invoking the Cursor Service. Command object property settings are always passed to the underlying data provider regardless of cursor location. Therefore, you can also use a command object to set the properties at any time.
| Property Name | Description |
|---|---|
| Auto Recalc (DBPROP_ADC_AUTORECALC) |
For recordsets created with the Data Shaping Service, this value indicates how often calculated and aggregate columns are calculated. The default (value=1) is to recalculate whenever the Data Shaping Service determines that the values have changed. If the value is 0, the calculated or aggregate columns are only calculated when the hierarchy is initially built. |
| Batch Size (DBPROP_ADC_BATCHSIZE) |
Indicates the number of update statements that can be batched before being sent to the data store. The more statements in a batch, the fewer round trips to the data store. |
| Cache Child Rows (DBPROP_ADC_CACHECHILDROWS) |
For recordsets created with the Data Shaping Service, this value indicates whether child recordsets are stored in a cache for later use. |
| Cursor Engine Version (DBPROP_ADC_CEVER) |
Indicates the version of the Cursor Service being used. |
| Maintain Change Status (DBPROP_ADC_MAINTAINCHANGESTATUS) |
Indicates the text of the command used for resynchronizing a one or more rows in a multiple table join. |
| Optimize | Indicates whether an index should be created. When set to True, authorizes the temporary creation of indexes to improve the execution of certain operations. |
| Reshape Name | Indicates the name of the Recordset. May be referenced within the current, or subsequent, data shaping commands. |
| Resync Command | Indicates a custom command string that is used by the Resync method when the Unique Table property is in effect. |
| Unique Catalog | Indicates the name of the database containing the table referenced in the Unique Table property. |
| Unique Schema | Indicates the name of the owner of the table referenced in the Unique Table property. |
| Unique Table | Indicates the name of the one table in a Recordset created from multiple tables that may be modified by insertions, updates, or deletions. |
| Update Criteria (DBPROP_ADC_UPDATECRITERIA) |
Indicates which fields in the WHERE clause are used to handle collisions occurring during an update. |
| Update Resync (DBPROP_ADC_UPDATERESYNC) |
Indicates whether the Resync method is implicitly invoked after the UpdateBatch method (and its behavior), when the Unique Table property is in effect. |
You may also set or retrieve a dynamic property by specifying its name as the index to the Properties collection. For example, get and print the current value of the Optimize dynamic property, then set a new value, like this:
Debug.Print rs.Properties("Optimize")
rs.Properties("Optimize") = True
Built-in Property Behavior
The Cursor Service for OLE DB also affects the behavior of certain built-in properties.
| Property Name | Description |
|---|---|
| CursorType | Supplements the types of cursors that are available for a Recordset. |
| LockType | Supplements the types of locks available for a Recordset. Enables batch updates. |
| Sort | Specifies one or more field names that the Recordset is sorted on, and whether each field is sorted in ascending or descending order. |
Method Behavior
The Cursor Service for OLE DB enables or affects the behavior of the Field object's Append method; and the Recordset object's Open, Resync, UpdateBatch, and Save methods.