Part IV: Professional Database Development
862
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 prac-
tices 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 assign-
ing values to variables that haven’t been dimmed. To make sure that all variables in your applica-
tion 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 24.1 shows the relative
speed of the different data types available in Access.
TABLE 24.1Data 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