Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Speed Up Iterative Processes in Visual Basic for Applications

ACC2000: How to Speed Up Iterative Processes in Visual Basic for Applications

View products that this article applies to.

Summary

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

↑ Back to the top

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 sample code 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 sample code 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).

↑ Back to the top

References

For more information about transaction processing, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type Transactions in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

↑ Back to the top

Keywords: KB210408, kbprogramming, kbinfo, kbhowto

↑ Back to the top

Article Info
Article ID : 210408
Revision : 4
Created on : 10/11/2006
Published on : 10/11/2006
Exists online : False
Views : 396