Solved Caching recordset in MS Access VBA

[tvanstiphout]

On the point of DAO caching, I did some research last night, and it is a real thing:
I linked to a SQL Server table with Order Details, and opened a DAO recordset with various cache sizes and scrolled forward for 10K records, while also running SQL Server profiler so I could see the traffic Access was generating. Below are the results. I conclude a cache size of 100 is really making a difference. I learned something, so Thank You!

 

[tvanstiphout]

I think the effectiveness is dependent on the number of times a cached record is reused. If you have a large set of records and never reuse any of them, no cache would help. Can you describe your query? How many rows in the table? How many rows retrieved? Is each iteration of the fetch retrieving the same set of records?

Hi Pat,
I'm assuming this was directed at me, with regards to my testing of DAO caching.

Re "If you have a large set of records and never reuse any of them, no cache would help": thas was not my experience. In my tests I
scrolled forward ONCE for 10K records. The number of server calls depends on the cache size.

Re "Can you describe your query?": I opened a dynaset on the entire table.

Re "How many rows in the table?": from my previous post you can surmise >10K. The actual count was 120K.

Re "How many rows retrieved?": from my previous post you can surmise 10K. The matter is: how many server calls was that.

Re "Is each iteration of the fetch retrieving the same set of records?": now you're warm. The "fetch" size is the cache size.

Actual code follows.

Sub testDaoCache()
    Const cCacheSize        As Integer = 1200     'The value of the CacheSize property must be between 5 and 1200, but not greater than available memory will allow. A typical value is 100. A setting of 0 turns off caching.
    Dim rs                  As DAO.Recordset
    Dim lngStart            As Long
    Dim lngCount            As Long

    Set rs = CurrentDb.OpenRecordset("Sales_SalesOrderDetail", dbOpenDynaset, dbSeeChanges)       'Sales_SalesOrderDetail: 120K records.
    'Caching recordset
    With rs
        .CacheStart = .Bookmark
        .CacheSize = cCacheSize
        .FillCache                  'ODBC linked tables only.

        lngStart = GetTickCount()
        .MoveFirst
        While Not lngCount > 10000
            .MoveNext
            lngCount = lngCount + 1
            If lngCount Mod cCacheSize = 0 Then
                .CacheStart = .Bookmark
                .FillCache
            End If
        Wend
        Debug.Print Time$, "Cache Size: " & cCacheSize, "Delta time: " & GetTickCount() - lngStart & " msec."
    End With
End Sub