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
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
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.
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
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!
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?
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
