Microsoft Access 2010 Bible

Chapter 24: Optimizing Access Applications

863

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 vari-

ables, 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 mem-

ory. By dynamically adjusting the size of the arrays, you can ensure that only the amount of mem-

ory 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 sit-

uations like the ones discussed here.