MS-Access / Getting Started

---

Getting the most from your modules

An area where you'll often be able to use smart optimization techniques is in your modules. For example, in code behind forms, use the Me keyword when referencing controls. This approach takes advantage of the capabilities of Access. Using Me is faster than creating a form variable and referencing the form in the variable. Other optimization techniques are simply smart coding practices that have been around for many years. Try to use the optimum coding technique at all times. When in doubt, try different methods to accomplish a task and see which one is fastest.

Tip: Consider reducing the number of modules and procedures in your application by consolidating them whenever possible. A small memory overhead is incurred for each module and procedure that you use, so consolidation may free up some memory. When doing this, though, keep in mind the discussion in the "Understanding Module Load on Demand" section.

Using appropriate data types

You should always explicitly declare variables using the Dim function instead of arbitrarily assigning values to variables that haven't been dimmed. To make sure that all variables in your application are explicitly declared, choose Tools → Options in the VBA editor window, select the Editor tab, and then set the Require Variable Declarations option on the tab.

Note: If you forgot to set the Require Variable Declarations option before you started coding, you'll need to add the line of code Option Explicit to the top of each existing module.

Tip: Use integers and long integers rather than singles and doubles when possible. Integers and long integers use less memory, and they take less time to process than singles and doubles do. Table-1 shows the relative speed of the different data types available in Access.

TABLE-1: Data Types and Their Mathematical Processing Speed

Data Type 		Relative Processing Speed
Integer/Long 		Fastest
Single/Double 		Next to fastest
Currency 		Next to slowest
Variant 		Slowest

In addition to using integers and long integers whenever possible, you should also use integer math rather than precision math when applicable. For example, to divide one long integer by another long integer, you can use the following statement:

x = Long1 / Long2

This statement is a standard math function that uses floating-point math. You can perform the same function by using integer math (the backward slash specifies integer division):

x = Long1 \ Long2

Of course, integer math isn't always applicable. It is, however, commonly applied when returning a percentage. For example, the following expression returns a percentage:

x = Total / Value

However, you can perform the same function using integer division by first multiplying the Total by 100 and then using integer division like this:

x = (Total * 100) \ Value

You should also use string functions ($) where applicable. When you're manipulating string variables, use the string functions (for example, Str$()) as opposed to their variant counterparts (Str()). If you're working with variants, use the non-$ functions. Using string functions when working with strings is faster because Access doesn't need to perform type conversions on the variables.

When you need to return a substring by using Mid$(), you can omit the third parameter to have the entire length of the string returned. For example, to return a substring that starts at the second character of a string and returns all remaining characters, use a statement like this:

strReturn = Mid$(strMyString, 2)

When using arrays, use dynamic arrays with the Erase and ReDim statements to reclaim memory. By dynamically adjusting the size of the arrays, you can ensure that only the amount of memory needed for the array is allocated.

Tip: In addition to using optimized variables, consider using constants when applicable. Constants can make your code easier to read and won't slow your application.

Writing faster routines

You can make your procedures faster by optimizing the routines that they contain in a number of ways. By keeping performance issues in mind as you develop, you'll find and take advantage of situations like the ones discussed here.

Some Access functions perform similar processes but vary greatly in execution time. You probably use one or more of these regularly, and knowing the most efficient way to perform these routines can greatly affect your application's speed:

• The IIF() function is much slower than If...Then...Else.
• The With and For Each functions accelerate manipulating multiple objects and their properties.
• Change a variable with Not instead of using an If . . . Then statement. (For example, use x = Not(y) instead of If y = True then x= False.)
• Instead of comparing a variable to the value True, use the value of the variable. (For example, instead of If X = True then . . ., use If X then . . .)
• Use the Requery method instead of the Requery action. The method is significantly faster than the action.
• When using OLE automation, resolve references by declaring variables as specific object types, rather than creating object references at runtime by using the GetObject or CreateObject functions. For example, create object variables using the following syntax:

  Dim WordObj As Word.Application
  This statement is considerably faster than using CreateObject:
  Set WordObj = CreateObject("Word.Application")