One of the slowest and most inefficient ways to process server data is to open a dynaset on an attached ODBC table and then proceed to move through it. For example, you should avoid doing the following:
' DO NOT DO THIS, ESPECIALLY with ODBC data!
' Use SQL Statements Instead. For example:
' MyDB.Execute "UPDATE Employees SET Title = 'Account Executive'
' WHERE Title = 'Sales Representative'"
Dim MyDB As Database, MyRS As Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyRS = MyDB.OpenRecordset("Employees", DB_OPEN_DYNASET)
...
MyRS.MoveFirst ' Locate first record.
Do Until MyRS.EOF ' Begin loop.
If MyRS!Title = "Sales Representative" Then ' Check title.
MyRS.Edit ' Enable editing.
MyRS!Title = "Account Executive" ' Change title.
MyRS.Update ' Save changes.
End If
MyRS.MoveNext ' Locate next record.
Loop ' End of loop.
MyRS.Close ' Close table.
Of course the above code would execute much more efficiently with an appropriate SQL statement, especially as a pass-through query (see elsewhere in this document). Even though the above code example is an obvious candidate for being replaced, some situations may require that you write code similar to this. If you need to, then you should use the Cache properties and methods in DAO.
Microsoft Access 2.0's CacheSize and CacheStart properties let you store all or part of the data contained in a dynaset-type Recordset in local memory. Local caching of rows dramatically speeds up operations when traversing dynaset rows bi-directionally and shows significant improvements even when moving in a forward only direction.
To use caching, you specify the number of rows to be stored by the value of CacheSize (Long) and the beginning row by the bookmark stored as the value of the CacheStart (String) property. Applying the FillCache method fills the cache with server data. Fetches within the cache boundary occur locally, speeding display of the cached records in a datasheet or in a continuous form. The allowable range of the value of CacheSize is between 5 and 1,200 records; if the size of the cache exceeds available memory, the excess records spill into a temporary disk file. Typically, you set the value of CacheSize to 100. To recover the cache memory, set CacheSize = 0.
For example, the above code would become:
' Assumes Employees is an attached ODBC table
Const EmpCache% = 100 ' # of employee recs to cache
Dim MyDB As Database, MyRS As Recordset, Dim I as Integer
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyRS = MyDB.OpenRecordset("Employees", DB_OPEN_DYNASET)
MyRS.CacheSize = EmpCache
I = 0
Do Until MyRS.EOF
If I Mod EmpCache = 0 Then
MyRS.CacheStart = MyRS.Bookmark
MyRS.FillCache
End If
If MyRS!Title = "Sales Representative" Then
MyRS.Edit
MyRS!Title = "Account Executive"
MyRS.Update
End If
MyRS.MoveNext
I = I + 1
Loop
MyRS.Close
As shown above, once you establish the cache, you need to keep moving its location to stay synchronized with the set of records you're working with. Using a cache with the example above can provide twice the performance of not specifying cache. If the application requires backward scrolling within the cached region, the performance improvements will be even larger. Depending on your scenario, using a cache may be faster than using a read-only, forward-only snapshot (especially if the snapshot contains memo or long binary fields that may only be referenced occasionally).
The size of the cache you use will be determined by the application needs. For example, if you are displaying these records to the user, then you might use a cache size determined by the number of rows they can have on the screen. If the code has no user interaction, then a tradeoff between local memory availability, network traffic, row size, and fetch delay can be made, usually by experimentation.