Solved Caching recordset in MS Access VBA (1 Viewer)

CJ_London

Super Moderator
Staff member
Local time
Today, 03:43
Joined
Feb 19, 2013
Messages
16,762
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

Member
Local time
Today, 05:43
Joined
Jan 21, 2020
Messages
408
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

Code:
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

Isam error.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:43
Joined
Feb 19, 2002
Messages
43,854
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

Super Moderator
Staff member
Local time
Today, 03:43
Joined
Feb 19, 2013
Messages
16,762
not sure where you are getting your idea of a discoonected recordset - see this link for an example

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

tvanstiphout

Active member
Local time
Yesterday, 19:43
Joined
Jan 22, 2016
Messages
291
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!

1715712247328.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:43
Joined
Feb 19, 2002
Messages
43,854
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

Active member
Local time
Yesterday, 19:43
Joined
Jan 22, 2016
Messages
291
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.
Code:
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
 

Users who are viewing this thread

Top Bottom