CacheSize, CacheStart Properties

Applies To

Dynaset-Type Recordset Object, Recordset Object.

CacheSize — sets or returns a value that specifies the number of records in a dynaset-type Recordset object containing data to be locally cached from an ODBC data source.
CacheStart — sets or returns a value that specifies the bookmark of the first record in the Recordset object to be cached.

Settings and Returned Values

The setting or return value is a number or string expression. The setting for CacheSize specifies the number of records and must be between 5 and 1200, but not greater than available memory. A typical value is 100. A setting of 0 turns off caching. (Data type is Long.) The setting for CacheStart specifies a bookmark. (Data type is String.)

Remarks

Data caching improves the performance of an application that retrieves data from a remote server through dynaset-type Recordset objects. A cache is a space in local memory that holds the data most recently retrieved from the server in the event that the data will be requested again while the application is running. When data is requested, the Microsoft Jet database engine checks the cache for the requested data first rather than retrieving it from the server, which takes more time. Data that doesn't come from an ODBC data source isn't saved in the cache.

Any ODBC data source, such as an attached table, can have a local cache. To create the cache, open a Recordset object from the remote data source, set the CacheSize and CacheStart properties, and then use the FillCache method or step through the records using the Move methods.

The CacheSize property setting can be based on the number of records your application can work with at one time. For example, if you're using a Recordset object as the source of the data to be displayed on screen, you could set its CacheSize property to 20 to display 20 records at one time.

The CacheStart property setting is the bookmark of the first record in the Recordset object to be cached. You can use the bookmark of any record to set the CacheStart property. Make the record you want to start the cache with the current record, establish a bookmark for that record, and set the CacheStart property to the bookmark.

The Jet database engine requests records within the cache range from the cache, and it requests records outside the cache range from the server.

Notes

Records retrieved from the cache don't reflect changes made concurrently to the source data by other users.
To force an update of all the cached data, set the CacheSize property of the Recordset object to 0, set it to the size of the cache you originally requested, and then use the FillCache method.

See Also

Bookmark Property, Bookmarkable Property, FillCache Method.

Specifics (Microsoft Access)

When you use the CacheStart property in a Microsoft Access module, you must include an Option Compare Binary statement in the Declarations section of the module. The bookmark set or returned by the CacheStart property is a Variant array of Byte data, so the string comparison method for the module must be binary. If a bookmark is evaluated with a text-based string comparison method, such as the Option Compare Text statement or the default setting for the Option Compare Database statement, the current record may be set to an incorrect record.

Example (Client/Server)

This example moves through all records in a Recordset twice; once with no cache and once with a 50 record cache. The example then displays the performance statistics for the uncached and cached runs through the Recordset.

Dim wspDefault As Workspace, dbsPubs As Database
Dim tdfPerformanceTest As TableDef, rstRemote As Recordset
Dim strNoCache As String, strCache As String

Set wspDefault = DBEngine.Workspaces(0)
Set dbsPubs = wspDefault.OpenDatabase("PUBLISH.mdb")
Set tdfPerformanceTest = dbsPubs.CreateTableDef("TestData")

tdfPerformanceTest.Connect = _     "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
tdfPerformanceTest.SourceTableName = "dbo.roysched"
dbsPubs.TableDefs.Append tdfPerformanceTest
ConnectSource = True
Set rstRemote = dbsPubs.OpenRecordset("TestData")

MsgBox "This example moves through all records in the Recordset " & _
    "twice; once with no cache and once with a 50 record cache."
' Start uncached run
tmStart = Timer
For i% = 1 To 2
    rstRemote.MoveFirst
    While Not rstRemote.EOF
        v = rstRemote(0)
        rstRemote.MoveNext
    Wend
Next i%
tmEnd = Timer
strNoCache = "Time without caching: " & Format$(tmEnd - tmStart) & _
    Chr$(13) & Chr$(10)
' Start cached run
rstRemote.MoveFirst
rstRemote.CacheStart = rstRemote.Bookmark
rstRemote.CacheSize = 50
tmStart = Timer
For i% = 1 To 2
    rstRemote.MoveFirst
    While Not rstRemote.EOF
        v = rstRemote(0)
        rstRemote.MoveNext
    Wend
Next i%
tmEnd = Timer
strCache = "Time with 50 record cache: " & Format$(tmEnd - tmStart) & _
    Chr$(13) & Chr$(10)
' Display performance results
MsgBox "Caching Performance Results:" & Chr$(13) & Chr$(10) & _
    strNoCache & strCache
rstRemote.Close
dbsPubs.Close

Example (Microsoft Access)

The following example opens a Recordset object from an attached Orders table that is stored on an ODBC server. The procedure next sets the CacheSize and CacheStart properties, and then uses the FillCache method to cache data from the server to the database.

Sub CacheData()
    Dim rst As Recordset, dbs As Database

    ' Return Database variable pointing to current database.
    Set dbs = CurrentDb
    ' Open local dynaset-type Recordset object.
    Set rst = dbs.OpenRecordset("Orders", DbOpenDynaset)
    ' Locate record with OrderID of 11000.
    rst.FindFirst "OrderID = 11000"
    ' Start caching records beginning with OrderID 11000.
    rst.CacheStart = rst.Bookmark
    rst.CacheSize = 12            ' Set cache size to 12 records.
    rst.FillCache            ' Fill cache.
    ' Display rows.
    .
    .
    .
End Sub