ACC: Speeding Up Iterative Processes in Visual or Access Basic

Last reviewed: August 29, 1997
Article ID: Q112724

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

To speed up iterative (looping) processes through large numbers of rows using 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 only for illustrative purposes. 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 for 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).

REFERENCES

For more information about transaction processing, search the Help Index for "Transactions," or ask the Microsoft Access 97 Office Assistant.

Additional query words: queries programming
Keywords : kbprg PgmLoop PgmOthr PgmPrfm
Version : 2.0 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbinfo

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.