Microsoft KB Archive/112724

= ACC: Speeding Up Iterative Processes in Visual or Access Basic =

Article ID: 112724

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q112724





SUMMARY
To speed up iterative (looping) processes through large numbers of rows in Visual Basic for Applications (or Access Basic in version 2.0), declare all field references explicitly.



MORE INFORMATION
The following is a code example that does not iterate efficiently: Sub Slow Dim d As Database Dim r As Recordset Set d = CurrentDB Set r = d.OpenRecordset("Order Details") While Not r.EOF r.Edit r.Fields("Price") = r.Fields("Qty") * r.Fields("UnitCost") r.Update r.MoveNext Wend r.Close End Sub In the example above, the field variable "lookup" (that is, where the Visual Basic function equates variable names with database fields) for the three field variables Price, Qty, and UnitCost is performed in the same While loop in which the calculations are performed. In this configuration, both calculations and lookups must be performed inside the While loop, which is not an efficient design.

The following code example is more efficient: Sub Faster Dim d As Database Dim r As Recordset Dim Price As Field, Qty As Field, UnitCost As Field Set d = CurrentDB Set r = d.OpenRecordset("Order Detail") Set Price = r.Fields("Price") Set Qty = r.Fields("Qty") Set UnitCost = r.Fields("UnitCost") While Not r.EOF r.Edit Price = Qty * UnitCost r.Update r.MoveNext Wend r.Close End Sub This example runs faster because Visual 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.

NOTE: The code examples above are for illustrative purposes only. In some cases, an update query can accomplish the task faster, for example, when you need to modify one field based on another field (or fields). Also, speed differences are slight if you are manipulating only a few records.

Using the transaction processing features of Visual Basic (BeginTrans, CommitTrans, and Rollback) can also help optimize performance. These features enable Microsoft Access to cache information and reduce disk input and output (I/O).

