Using CacheSize

Use the CacheSize property to control how many records to retrieve at one time into local memory from the provider. For example, if the CacheSize is 10, after first opening the Recordset object, the provider retrieves the first 10 records into local memory. As you move through the Recordset object, the provider returns the data from the local memory buffer. As soon as you move past the last record in the cache, the provider retrieves the next 10 records from the data source into the cache.

Note   CacheSize is based on the Maximum Open Rows provider-specific property (in the Properties collection of the Recordset object). You cannot set CacheSize to a value greater than Maximum Open Rows. To modify the number of rows that can be opened by the provider, set Maximum Open Rows.

The value of CacheSize can be adjusted during the life of the Recordset object, but changing this value only affects the number of records in the cache after subsequent retrievals from the data source. Changing the property value alone will not change the current contents of the cache.

If there are fewer records to retrieve than CacheSize specifies, the provider returns the remaining records and no error occurs.

A CacheSize setting of zero is not allowed and returns an error.

Records retrieved from the cache do not reflect concurrent changes that other users made to the source data. To force an update of all the cached data, use the Resync method.

If CacheSize is set to a value greater than 1, the navigation methods (Move, MoveFirst, MoveLast, MoveNext, and MovePrevious) may result in navigation to a deleted record, if deletion occurs after the records were retrieved. After the initial fetch, subsequent deletions will not be reflected in your data cache until you attempt to access a data value from a deleted row. However, setting CacheSize to 1 eliminates this issue because deleted rows cannot be fetched.