Solved Caching recordset in MS Access VBA (3 Viewers)

[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