When dividing integers, use integer division. A majority of the division operations performed by your application are probably done on integer values. Many developers use the slash (/) operator to divide two numbers, but this operator is optimized for floating-point division. If you're dividing integers, you should use the backslash (\) integer division operator instead. With \, Access works at the integer level instead of the floating-point level, so computation is faster. (Of course, this is useful only if you're assigning the results of the division operation to an integer. If you care about the fractional portion of the division, you'll need to use floating-point math and the / operator after all.) For example, instead of:

intX = intY / intZ

use:

intX = intY \ intZ

Use specific data types instead of variants. Variants offer convenience at the expense of performance. Every time you refer to a variant, Access needs to perform type conversion to ensure the data is in the correct format. By using the data type that matches your variable, you eliminate the need for this type conversion, and your code runs faster. In addition, a variant variable is twice as large as an integer (on a 32-bit operating system) and thus takes longer to manipulate.

Test for blank strings using the Len function. You probably have code that tests for blank strings by comparing them to an empty string (""). However, because Access stores the length of the string as the first byte in the string, testing for a length of zero using the Len function is always faster. Instead of:

If strTemp = "" Then
   MsgBox "The string is blank"
End If

use:

If Len(strTemp) = 0 Then
   MsgBox "The string is blank"
End If

If you refer to an object more than once in a section of code, assign it to an object variable. Every time you reference an object, Access has to perform some work to figure out which object you are referring to. This adds overhead to your code each time the object is referenced. But if you assign the object to an object variable, Access "finds" the object once and caches the reference in memory. So after the first reference, you can refer to the object through the object variable and your code will run faster. For example, instead of this code:

Dim strTmp As String
Dim lngCount As Long

For lngCount = 0 To acbcMaxIterations / 2
   strTmp = DBEngine.Workspaces(0).Groups(0).Name
Next lngCount

use:

Dim grp As DAO.Group
Dim strTmp As String
Dim lngCount As Long

Set grp = DBEngine.Workspaces(0).Groups(0)

For lngCount = 0 To acbcMaxIterations / 2
   strTmp = grp.Name
Next lngCount

We created two variations of this test. First, we changed the function to refer to a control on an open form instead of a DAO group. The cached reference version of the code was 2.8 times faster—significantly improved, but not of the same magnitude as the DAO group comparison. Second, we compared using an object variable against using the VBA With...End With construct (without an object reference). With...End With was slower than using an object variable, but still much faster than using neither an object variable nor With...End With.

Don't worry about comments. In VBA the use of comments exacts no measurable performance penalty, so there's no excuse for omitting them!

Use If...Then...Else instead of the IIf function. By replacing IIf statements with the equivalent If...Then...Else statement, your code will run faster. For example, instead of:

MsgBox IIf(intX = 1, "One", "Not One")

use:

If intX = 1 Then
   MsgBox "One"
Else 
   MsgBox "Not One"
End If

When initializing a string so that it's empty, don't use a literal value (""). Instead, use the built-in vbNullString constant. You'll get better performance, as the test demonstrates.