The CacheSize property on a Recordset object indicates the number of records from a Recordset object that are cached locally in memory. This property sets or returns a Long value that must be greater than zero. The default value for the CacheSize property is 1.
previousSize = recordset.CacheSize
recordset.CacheSize = 1
The CacheSize property is used 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 the CacheSize 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 the CacheSize property specifies, the provider returns the remaining records; no error occurs.
A CacheSize setting of zero is not allowed and returns an error. Non-bookmarkable files cannot have the CacheSize property set to greater than one, or an error will occur.
It is strongly recommended that a CacheSize of 1 be used with the OLE DB Provider for AS/400 and VSAM. If the CacheSize is set greater than 1, it is possible for the local data cached in memory to be out of date from changes made by other users on the host.