CacheSize Property
Indicates the number of records from a Recordset object that are cached locally in memory.
Settings and Return Values
Sets or returns a Long value that must be greater than 0. Default is 1.
Remarks
Use the CacheSize property to control how many records the provider keeps in its buffer and how many to retrieve at one time into local memory. 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.
The value of this property 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 don't 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 one, 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 one eliminates this issue since deleted rows cannot be fetched.