To speed up iterative (looping) processes through large numbers of rows using Access Basic, declare all field references explicitly.
The following is an example of Access Basic code that can be improved by using direct Field references:
While Not rstOrd.EOF rstOrd.Edit rstOrd.Fields!Price = rstOrd!Qty * rstOrd!UnitCost rstOrd.Update rstOrd.MoveNext Wend
In the example above, the field variable "lookup" (that is, where Access Basic equates variable names with database fields) for the three field variables Price, Qty, and UnitCost is performed in the same While loop where the calculations are performed. In this configuration, both calculations and lookups must be performed inside the While loop, which is not an efficient design. This is how the changes might look:
The following is an example of Access Basic code that is more efficient:
Dim Price As Field, Qty As Field, UnitCost As Field Set Price = rstOrd!Price Set Qty = rstOrd!Qty Set UnitCost = rstOrd!UnitCost rst.BeginTrans While Not rstOrd.EOF rstOrd.Edit Price = Qty * UnitCost rstOrd.Update rstOrd.MoveNext Wend rst.CommitTrans
This example runs faster because Access Basic performs the field lookup only once for each field and completes it before executing the loop. A direct reference to each of the three fields is then stored in the three field variables Price, Qty, and UnitCost. As a result, no lookup is required in the While loop, and the field values are accessed and manipulated directly.
The code examples above are illustrative. In some cases, an Update query can be a faster way to accomplish the task (see tip #11). Also, speed differences will be slight for small numbers of records. Note the use of tip #12 included in the revised example.