Solved Caching recordset in MS Access VBA

[CJ_London]

The entire program is using DAO to switch to ADO means reprograming the codes again

You are talking about a single recordset, not the whole app. Ado won’t work for reports and for forms, the default filtering and sorting won’t work. But for a single recordset being used in vba it is fine

 

[nector]

Okay for curiosity sake I seam to develop some ideas, how about working directly with the server before coming to the said record set instead of using link tables string I try to open the tables direct from the server.

The challenge I have is that I'm not using windows directly as result I'm getting an ISAM Error 3170

Public Function linktables()
Dim strcnn
strcnn = "Data Source=tcp:s26.winhost.com;Initial Catalog=DB_155528_accounts;User ID=DB_155528_accounts_user;Password=*******;Integrated Security=False;"
DoCmd.TransferDatabse aclink,strccn,acTable,"tblaccounts","tblaccounts"
End Function

If this worked I wanted to use now an autoexec macro to relink all the table.

Question

Is there a way to accomplish this with cloud linked tables not with LAN, if this works then I will need to convert that query into a View

 

[Pat Hartman]

A linked table is a linked table regardless of whether it is a permanent link or one made on the fly. Obviously a permanent link is more efficient.

If you host the cloud yourself, you can probably optimize it so that linking to Azure will work OK as long as your app doesn't do things like binding forms to tables or to queries without criteria.

 

[CJ_London]

not sure where you are getting your idea of a discoonected recordset - see this link for an example

Tutorials >> Using Disconnected Recordsets

Tutorials » Using Disconnected Recordsets Anyone who works with databases will probably at some stage have occasion to work with a particular Recordset over a period of time. However, it would not be practical or desirable to maintain an open connection to the database throughout as this would...

www.devguru.com

assuming you have a linked table to your azure BE then the connection string is simply currentdb.connection

 

[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!

 

[Pat Hartman]

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?

 

[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