Microsoft Access 2.0's CacheSize and CacheStart properties let you store all or a part of the data contained in a dynaset-type Recordset in local memory. Local caching of rows dramatically speeds up operations when traversing dynaset rows bi-directionally and shows significant improvements even when moving in a forward only direction.
To use caching, you specify the number of rows to be stored by the value of CacheSize (Long) and the beginning row by the bookmark stored as the value of the CacheStart (String) property. Applying the FillCache method fills the cache with server data. Fetches within the cache boundary occur locally, speeding display of the cached records in a datasheet or in a continuous form. The allowable range of the value of CacheSize is between five and 1,200 records; if the size of the cache exceeds available memory, the excess records spill into a temporary disk file. Typically, you set the value of CacheSize to 100. To recover the cache memory, set CacheSize = 0.
Once you establish the cache, you need to keep moving its location to stay synchronized with the set of records you're working with. Using a cache can provide twice the performance of not specifying cache. If the application requires backward scrolling within the cached region the performance improvements will be even larger. Depending on your scenario, using a cache may be faster than using a read-only forward-only snapshot (especially if the snapshot contains memo or long binary fields which may only be referenced occasionally).
The size of the cache you use will be determined by the application needs. For example, if you are displaying these records to the user, then you might use a cache size determined by the number of rows they can have on the screen. If the code has no user interaction, then a tradeoff between local memory availability, network traffic, row size, and fetch delay can be made, usually by experimentation.