The bottom line is that Windows 95, Windows 98, Windows NT, and Access 2002 all crave hardware—the more, the better. The faster your users’ machines are, and the more memory they have, the better your applications will run. Obtaining additional hardware might not be the least expensive solution, but it certainly is the quickest and easiest thing you can do to improve the performance of your application.

In addition to the more obvious measures just outlined, some minor software tweaking can go a long way toward improving performance. Adjusting several settings in the Windows registry can dramatically improve performance. These changes all involve the registry’s ISAM section. The properties you might want to change include MaxBufferSize and ReadAheadPages. Both these settings determine how the Jet Engine uses memory.

MaxBufferSize controls the maximum size of the Jet Engine’s internal cache. By default, it is set to optimize performance on most machines. It does this by reading data in 2KB pages, placing the data in a memory cache. The data in the cache is readily available to forms, reports, tables, and queries. Lowering the value for MaxBufferSize frees memory for other tasks. This might be helpful on a machine with a minimum memory configuration.

ReadAheadPages controls the number of 4KB data pages that the Jet Engine reads ahead when performing sequential page reads. This number can range from 0–31, with the default at 16. The higher the number, the more efficient Access is at reading ahead so that data is available when you need it. The lower this number, the more memory is freed up for other tasks.

As you configure any of these settings, remember that what is good for one machine is not necessarily good for the next. The settings for each machine must be optimized with its unique hardware configuration in mind.

In essence, “be normal” means normalize your tables—that is, consolidate common data in related tables. Processing the same data that appears in multiple places can significantly slow down your application. This is due to both the volume of data that is generated, as well as the need to update all copies of the data whenever the data changes. Suppose a company address appears in both the Customer table and the Orders table. If the company address changes, it must be changed in both the Customer table and in the Orders table. This information should be included only in the Customer table. Queries should be used to combine the address and order data when needed.

When it comes to performance, unfortunately, there are no hard-and-fast rules. Although, most of the time, you gain performance by normalizing your data structure, denormalizing your structure can help at times. This generally is the case when you find yourself creating a particular join over and over again. Another example is an accounting application in which you need to be able to readily see the total amount that a customer owes. Rather than evaluating all the open invoices each time you move to a customer record, you can store the total amount that the customer owes on the customer record. Of course, this requires that you update the summarized figure whenever the customer is billed or makes a payment. In summary, you can try denormalizing the data to see whether dramatic performance improvements result. Remember that denormalization has definite downsides regarding data integrity and maintenance.

It is amazing how far an index can go in improving performance. Any fields or combination of fields on which you search should be included in an index. You should create indexes for all columns used in query joins, searches, and sorts. You should create primary key indexes rather than unique indexes, and unique indexes rather than non-unique indexes. It is not necessary to create an index for the foreign key field in a one-to-many relationship. The index is created automatically by Access when the relationship is established. Furthermore, there is no benefit to creating an index on a field containing highly repetitive data. An example is a state field in a customer table where all the customers are located in one of two states. Although indexes can be overused, when used properly, the performance improvements rendered by indexes are profound.

When defining a field, select the shortest data type available for the storage of the data. If you will be storing a code between 1 and 10 within the field, for example, there is no reason to select Double for a numeric field.

Variant variables are the slowest for the operating system to process; they carry a lot of overhead because they are resolved at runtime. Remember that this statement declares a variant type of variable:

Dim intCounter

To strong-type this variable as an integer, for example, your code must be modified to look like this:

Dim intCounter As Integer

Not only should you strong-type your variables, but you also should use the smallest data type possible. Remember that data types such as Boolean, Byte, Integer, and Long are the smallest and therefore the fastest to resolve. These are followed by Single, Double, Currency, and (finally) Variant. Of course, if you must store very large numbers with decimal points in a variable, you cannot pick Single. Just keep in mind that it is wise to select the smallest data type appropriate for the use of the variable. Listing 17.1 provides code that illustrates the difference between using a variant and a long integer.

Private Sub cmdVariantBenchMark_Click()
    Dim vntAny
    Dim intCounter As Long
    Dim dblStartTime As Double
    Dim dblTime1 As Double
    Dim dblTime2 As Double

    'Execute loop with variant
    dblStartTime = Timer

    Do Until vntAny = 500000
        vntAny = vntAny + 1
    Loop

    dblTime1 = Timer - dblStartTime

    'Execute loop with integer
    dblStartTime = Timer

    Do Until intCounter = 500000
        intCounter = intCounter + 1
    Loop

    dblTime2 = Timer - dblStartTime

    'Display time and percent differences
    Me.txtSlow = dblTime1
    Me.txtOptimized = dblTime2
    Me.txtPercent = (1 - (dblTime1 / dblTime2)) * 100

End Sub

The code, found in the form frmBenchmark in the Chap17Ex.mdb sample database, loops using a variant, and then a long integer. The amount of time required to execute each loop is displayed, along with the percent difference between the two techniques.

Just as using the General variant data type is inefficient, using generic object variables also is inefficient. This is because the compiler needs to evaluate their type at runtime. The MakeItBold subroutine uses a generic object variable, as shown in Listing 17.2.

Private Sub cmdMakeBold_Click()
    Call MakeItBold(Screen.PreviousControl)
End Sub

Sub MakeItBold(ctlAny As Control)
    ctlAny.FontBold = True
End Sub

On the other hand, the SpecificBold subroutine uses a specific object variable, as Listing 17.3 shows.

Private Sub cmdSpecificBold_Click()
    Call SpecificBold(Screen.PreviousControl)
End Sub

Sub SpecificBold(txtAny As TextBox)
    txtAny.FontBold = True
End Sub

The difference is that the SpecificBold routine expects to receive only text boxes. It does not need to resolve the type of object it receives and therefore is more efficient.

This code is contained in the CHAP17EX.MDB database on the accompanying CD-ROM. You can find the example in frmObjVar.

The best way to truly compare using a specific control versus a generic control is to benchmark the techniques, as shown in Listing 17.4.

Private Sub cmdObjectTypes_Click()
    Dim intCounter As Long
    Dim dblStartTime As Double
    Dim dblTime1 As Double
    Dim dblTime2 As Double

    'Execute loop with generic control
    dblStartTime = Timer

    For intCounter = 1 To 5000
        Call MakeItBold(Me.txtOptimized)
    Next intCounter

    dblTime1 = Timer - dblStartTime

    'Execute loop with specific control
    dblStartTime = Timer

    For intCounter = 1 To 5000
        Call SpecificBold(Me.txtOptimized)
    Next intCounter

    dblTime2 = Timer - dblStartTime

    'Display time and percent differences
    Me.txtSlow = dblTime1
    Me.txtOptimized = dblTime2
    Me.txtPercent = (1 - (dblTime1 / dblTime2)) * 100

End Sub

The code, found in frmBenchmark, passes a text box to two different routines. The first routine receives any control as a parameter. The second routine receives only text boxes as a parameter. The benchmarks prove that routines that utilize specific object types take less time and therefore are more efficient.

There is a tendency to call out to procedures for everything. This is good from a maintenance standpoint but not from an efficiency standpoint. Each time VBA calls out to a procedure, additional time is taken to locate and execute the procedure. This is particularly evident when the procedure is called numerous times. The alternative is to use inline code. Executing inline code is more efficient than calling out to procedures because Access does not need to locate the code. The downside of inline code is that it is more difficult to maintain. You must decide how important maintainability is compared to speed.

Listing 17.5 shows the same code called as a routine and executed in-line. The benchmark shows that the inline code executes much more quickly.

Private Sub cmdInLine_Click()
    Dim dblAny As Double
    Dim intCounter As Long
    Dim dblStartTime As Double
    Dim dblTime1 As Double
    Dim dblTime2 As Double

    'Execute loop calling out to procedure
    dblStartTime = Timer

    For intCounter = 1 To 50000
        Call SmallRoutine
    Next intCounter

    dblTime1 = Timer - dblStartTime

    'Execute loop with in-line code
    dblStartTime = Timer

    For intCounter = 1 To 50000
        dblAny = 5 / 3
    Next intCounter

    dblTime2 = Timer - dblStartTime

    'Display time and percent differences
    Me.txtSlow = dblTime1
    Me.txtOptimized = dblTime2
    Me.txtPercent = (1 - (dblTime1 / dblTime2)) * 100
End Sub

Private Sub SmallRoutine()
    Dim dblAny As Double
    dblAny = 5 / 3
End Sub

This code is very inefficient:

If bFlag = True Then
  bFlag = False
Else
   bFlag = True
End If

It should be modified to look like this:

bFlag = Not bFlag

Besides requiring fewer lines of code, this expression evaluates much more quickly at runtime. Listing 17.6 proves that toggling the Boolean variable is a much more efficient approach to the problem than having to test each condition separately. This code is found in frmBenchmarks on the CD-ROM accompanying this book.

Private Sub cmdBooleans_Click()
    Dim boolAny As Boolean
    Dim intCounter As Long
    Dim dblStartTime As Double
    Dim dblTime1 As Double
    Dim dblTime2 As Double

    'Execute loop with If statement
    dblStartTime = Timer

    For intCounter = 1 To 100000
        If boolAny = True Then
            boolAny = False
        Else
            boolAny = True
        End If
    Next intCounter

    dblTime1 = Timer - dblStartTime

    'Execute loop toggling boolean
    dblStartTime = Timer
    For intCounter = 1 To 100000
        boolAny = Not boolAny
    Next intCounter

    dblTime2 = Timer - dblStartTime

    'Display time and percent differences
    Me.txtSlow = dblTime1
    Me.txtOptimized = dblTime2
    Me.txtPercent = (1 - (dblTime1 / dblTime2)) * 100

End Sub

The built-in collections are there whether or not you use them. By using For Each...Next and a collection of objects, you can write very efficient code, as shown in Listing 17.7.

Sub FormCaption()
   Dim frm As Form
   For Each frm In Forms
      frm.Caption = frm.Caption & " - " & CurrentUser()
   Next
End Sub

In this example, you use the Forms collection to quickly and efficiently loop through each form, changing the caption on its title bar. The code shown in Listing 17.8 illustrates the use of the Forms collection, as well as an alternative method of accomplishing the same task.

Private Sub cmdCollections_Click()
    Dim frm As Form
    Dim intNumForms As Integer
    Dim intLoop As Integer
    Dim intCounter As Long
    Dim dblStartTime As Double
    Dim dblTime1 As Double
    Dim dblTime2 As Double
    'Execute loop with For Next
    dblStartTime = Timer

    For intCounter = 1 To 50
        intNumForms = Forms.Count - 1
        For intLoop = 0 To intNumForms
            Forms(intLoop).Caption = "Hello"
        Next intLoop
    Next intCounter

    dblTime1 = Timer - dblStartTime

    'Execute loop with For Each
    dblStartTime = Timer

    For intCounter = 1 To 50
        For Each frm In Forms
            frm.Caption = "Hello"
        Next frm
    Next intCounter

    dblTime2 = Timer - dblStartTime

    'Display time and percent differences
    Me.txtSlow = dblTime1
    Me.txtOptimized = dblTime2
    Me.txtPercent = (1 - (dblTime1 / dblTime2)) * 100
End Sub

Without the For Each..Next loop, a variable must be used to loop through the forms. Notice that intNumForms is set equal to the number of forms in the Forms collection minus one. The loop goes from zero to the value stored in intNumForms, changing the caption of the specified form. Although the performance gains realized by using the Forms collection are not dramatic, you’ll probably agree that the Forms collection technique is much simpler to implement.

Using the Len function (as shown in Listing 17.9) is more efficient than testing for a zero-length string (as shown in Listing 17.10).

Sub SayNameLen(strName As String)
   If Len(strName) Then
      MsgBox strName
   End If
End Sub

Sub SayNameZero(strName As String)
   If strName <> "" Then
      MsgBox strName
   End If
End Sub

Listing 17.9 is easier for VBA to evaluate and therefore runs more quickly and efficiently. This is emphasized by the code shown in Listing 17.11 (located in frmBenchmark). The code shows two loops. One utilizes the Len function and the other does not. The benchmark proves that the routine that utilizes the Len function executes more quickly.

Private Sub cmdLen_Click()
    Dim dblStartTime As Double
    Dim dblTime1 As Double
    Dim dblTime2 As Double
    Dim strTextBoxValue As String

    strTextBoxValue = Me.txtOptimized

    'Execute loop with zero-length string
    dblStartTime = Timer

     For intCounter = 1 To 50000
         If strTextBoxValue <> "" Then
         End If
     Next intCounter

     dblTime1 = Timer - dblStartTime

     'Execute loop with Len
     dblStartTime = Timer

     For intCounter = 1 To 50000
         If Len(strTextBoxValue) Then
         End If
     Next intCounter

     dblTime2 = Timer - dblStartTime

     'Display time and percent differences
     Me.txtSlow = dblTime1
     Me.txtOptimized = dblTime2
     Me.txtPercent = (1 - (dblTime1 / dblTime2)) * 100

End Sub

This example is similar to the preceding one. It is better to evaluate for True and False (as shown in Listing 17.12) instead of 0 (as shown in Listing 17.13).

Sub SaySalaryTrue(lngSalary As Long)
    If lngSalary Then
        MsgBox "Salary is " & lngSalary
    End If
End Sub

Sub SaySalaryZero(lngSalary As Long)
    If lngSalary <> 0 Then
        MsgBox "Salary is " & lngSalary
    End If
End Sub

The code in Listing 17.12 runs more efficiently. The benchmark shown in Listing 17.14 provides an example. The lngSalary is evaluated against zero in the top loop. The bottom loop tests lngSalary against True. The second loop runs more quickly.

Private Sub cmdTrueFalse_Click()
    Dim intCounter As Long
    Dim lngSalary As Long
    Dim dblStartTime As Double
    Dim dblTime1 As Double
    Dim dblTime2 As Double

    'Execute loop with zero
    dblStartTime = Timer

    For intCounter = 1 To 50000
        If lngSalary <> 0 Then
        End If
    Next intCounter

    dblTime1 = Timer - dblStartTime

    'Execute loop with True/False
    dblStartTime = Timer

    For intCounter = 1 To 50000
        If lngSalary Then
        End If
    Next intCounter

    dblTime2 = Timer - dblStartTime

    'Display time and percent differences
    Me.txtSlow = dblTime1
    Me.txtOptimized = dblTime2
    Me.txtPercent = (1 - (dblTime1 / dblTime2)) * 100

End Sub

In versions of Access prior to Access 95, transactions dramatically improved performance. Using explicit transactions, the data is written to disk only once, after the CommitTrans. All changes between a BeginTrans and a CommitTrans are buffered in memory. Because disk access has the slowest throughput on a computer, this technique offered major performance benefits when it was introduced. The difference between Access 95 and all subsequent versions of Access is that the Jet 3.0, 3.5, 3.6 and 4.0 engines in the later versions implicitly buffer transactions. Most of the time, Jet’s own transaction-handling offers better performance than your own. At other times, you can improve on what Jet does. The only way you will know what works for your code is to do your own benchmarking. Each situation is different.

As you modify your subroutines and functions, you often declare a variable and then never use it. Each Dim statement takes up memory, whether or not you are using it. Furthermore, Declare statements, which are used to call external library functions, also take up memory and resources. You should remove these statements if they are not being used.

Most programmers experiment with various alternatives for accomplishing a task. This often involves creating numerous test subroutines and functions. The problem is that most people do not remove this code when they are done with it. This dead code is loaded with your application and therefore takes up memory and resources. Several third-party tools are available that can help you find both dead code and variable declarations. One tool that many people use is called Total Access Analyzer, by FMS, Inc.

If you are going to repeatedly refer to an object, you should declare an object and refer to the object variable rather than the actual control, as shown in Listing 17.15.

Forms!frmAny.txtHello.FontBold = True
Forms!frmAny.txtHello.Enabled = True
Forms!frmAny.txtHello.Left = 1
Forms!frmAny.txtHello.Top = 1

This is a very scaled-down example, but, if numerous properties are being changed, or if this code is being called recursively, an object variable can make the code more efficient, as Listing 17.16 shows.

Private Sub cmdChangeObject_Click()
    Dim txt As TextBox
    Set txt = Forms!frmHello.txtHello1
    txt.FontBold = True
    txt.Enabled = True
    txt.Left = 100
    txt.Top = 100
End Sub

The benchmark shown in Listing 17.17 contains two loops. The first loop sets four properties of the same control, explicitly referencing the control as each property is set. The second loop uses an object variable to accomplish the same task. The difference in performance between the two loops is somewhat dramatic.

Private Sub cmdObjectVariable_Click()
    Dim intCounter As Long
    Dim dblStartTime As Double
    Dim dblTime1 As Double
    Dim dblTime2 As Double

    'Execute loop without object variable
    dblStartTime = Timer

    For intCounter = 1 To 1000
        Forms.frmBenchMark.txtOptimized.FontBold = True
        Forms.frmBenchMark.txtOptimized.Enabled = True
        Forms.frmBenchMark.txtOptimized.Locked = False
        Forms.frmBenchMark.txtOptimized.BackStyle = vbNormal
    Next intCounter

    dblTime1 = Timer - dblStartTime

    'Execute loop with object variable
    dblStartTime = Timer

    For intCounter = 1 To 1000
        Dim txt As TextBox
        Set txt = Forms.frmBenchMark.txtOptimized
        txt.FontBold = True
        txt.Enabled = True
        txt.Locked = False
        txt.BackStyle = vbNormal
    Next intCounter

    dblTime2 = Timer - dblStartTime

    'Display time and percent differences
    Me.txtSlow = dblTime1
    Me.txtOptimized = dblTime2
    Me.txtPercent = (1 - (dblTime1 / dblTime2)) * 100


End Sub

Another way to optimize the code in the preceding example is to use a With...End With construct, as shown in Listing 17.18.

Private Sub cmdChangeObjectWith_Click()
    With Forms!frmHello.txtHello2
       .FontBold = True
       .Enabled = True
       .Left = 100
       .Top = 100
    End With
End Sub

The code in Listing 17.19 shows two different loops. The first loop explicitly references the text box four different times to set four different properties. The second loop uses a With statement to reference the same control and set the four properties. The code in the second loop executes much more efficiently.

Private Sub cmdWith_Click()
    Dim intCounter As Long
    Dim dblStartTime As Double
    Dim dblTime1 As Double
    Dim dblTime2 As Double

    'Execute loop without With statement
    dblStartTime = Timer

    For intCounter = 1 To 1000
        Forms.frmBenchMark.txtOptimized.FontBold = True
        Forms.frmBenchMark.txtOptimized.Enabled = True
        Forms.frmBenchMark.txtOptimized.Locked = False
        Forms.frmBenchMark.txtOptimized.BackStyle = vbNormal
    Next intCounter

    dblTime1 = Timer - dblStartTime

    'Execute loop with With statement
    dblStartTime = Timer

    For intCounter = 1 To 1000
        With Forms.frmBenchMark.txtOptimized
            .FontBold = True
            .Enabled = True
            .Locked = False
            .BackStyle = vbNormal
        End With
    Next intCounter

    dblTime2 = Timer - dblStartTime

    'Display time and percent differences
    Me.txtSlow = dblTime1
    Me.txtOptimized = dblTime2
    Me.txtPercent = (1 - (dblTime1 / dblTime2)) * 100

End Sub

Although both the object variable reference and the With statement significantly improve performance, Listings 17.17 and 17.19 can be further improved by resolving the object variable outside the loop. Listing 17.20 provides an example.

Private Sub cmdVariable_Click()
    Dim txtAny As TextBox
    Dim intCounter As Long
    Dim dblStartTime As Double
    Dim dblTime1 As Double
    Dim dblTime2 As Double

    'Execute loop without object resolution
    dblStartTime = Timer

    For intCounter = 1 To 1000
        Forms.frmBenchmark.txtOptimized.FontBold = True
        Forms.frmBenchmark.txtOptimized.Enabled = True
        Forms.frmBenchmark.txtOptimized.Locked = False
        Forms.frmBenchmark.txtOptimized.BackStyle = vbNormal
    Next intCounter

    dblTime1 = Timer - dblStartTime

    'Execute loop with object resolution
    dblStartTime = Timer

    Set txtAny = Forms.frmBenchmark.txtOptimized
    For intCounter = 1 To 1000
        With txtAny
            .FontBold = True
            .Enabled = True
            .Locked = False
            .BackStyle = vbNormal
        End With
    Next intCounter

    dblTime2 = Timer - dblStartTime

    'Display time and percent differences
    Me.txtSlow = dblTime1
    Me.txtOptimized = dblTime2
    Me.txtPercent = (1 - (dblTime1 / dblTime2)) * 100
End Sub

Notice that the object variable is resolved outside the loop. This loop executes significantly faster than the loops in Listings 17.17 and 17.19.

In the preceding example, you used Forms!frmHello.txtHello to refer to a control on the current form. It is more efficient to refer to the control as Me.txtHello because VBA searches only in the local name space. Although this makes your code more efficient, the downside is that the Me keyword only works within form, report, and class modules. It won’t work within standard code modules. This means that you cannot include the Me keyword in generic functions that are accessed by all your forms.

Several VBA functions come in two forms: one with a dollar sign ($) and one without. An example is Left(sName) versus Left$(sName). Whenever it is acceptable to return a string, it is more efficient to use the version with the dollar sign, which return strings rather than variants. When a string variable is returned, VBA doesn’t need to perform type conversions.

Array elements take up memory, whether or not they are being used. It therefore is sometimes preferable to use dynamic arrays. You can increase the size of a dynamic array as necessary. If you want to reclaim the space used by all the elements of the array, you can use the Erase keyword, as in this example:

Erase aNames

If you want to reclaim some of the space being used by the array without destroying data in the elements you want to retain, use Redim Preserve:

Redim Preserve aNames(5)

This statement sizes the array to six elements. (It is zero-based.) Data within those six elements is retained.

Constants improve both readability and performance. A constant’s value is resolved after compilation. The value the constant represents is written to code. A normal variable has to be resolved as the code is running because VBA needs to obtain the current value of the variable.

A bookmark provides you with the most rapid access to a record. If you are planning to return to a record, set a variable equal to that record’s bookmark, making it very easy to return to that record at any time. Listing 17.21 shows an example that uses a bookmark.

Sub BookMarkIt()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    Dim varBM As Variant

    rst.Open "tblProjects", CurrentProject.Connection, adOpenStatic
    varBM = rst.Bookmark
    Do Until rst.EOF
        Debug.Print rst!ProjectID
        rst.MoveNext
    Loop
    rst.Bookmark = varBM
    Debug.Print rst!ProjectID
End Sub

You can find this code in basOptimize of CHAP17EX.MDB. The bookmark is stored in a variable until the Do...Until loop executes. Then, the recordset’s bookmark is set equal to the value contained within the variable.

Object variables take up memory and associated resources. Their value should be set to Nothing when you are finished using them, for example,

Set oObj = Nothing

This conserves memory and resources.

Besides being easier to code, executing a stored query is much more efficient than looping through a recordset, performing some action on each record. Listing 17.22 shows an example that loops through a recordset.

Sub LoopThrough()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    rst.Open "tblProjects", CurrentProject.Connection, adOpenDynamic, _
    adLockOptimistic
    Do Until rst.EOF
        rst!ProjectTotalEstimate = rst!ProjectTotalEstimate + 1
        rst.UPDATE
        rst.MoveNext
    Loop
End Sub

This code, which is located in basOptimize of CHAP17EX.MDB, loops through a recordset, adding 1 to each project’s total estimate. Contrast this with the code in Listing 17.23.

Sub ExecuteQuery()
    Dim adoCat As ADOX.Catalog
    Dim cmd As ADODB.Command
    Set adoCat = New ADOX.Catalog
    Set cmd = New ADODB.Command

    Set adoCat.ActiveConnection = CurrentProject.Connection
    Set cmd = adoCat.Procedures("qupdLowerEstimate").Command
    cmd.Execute

End Sub

This code uses a command object to execute a stored query called qupdLowerEstimate. The query runs much more efficiently than the Do...Until loop shown in Listing 17.22.

Applications run slower when they are not compiled. Forms and reports load slower, and the application requires more memory. If you deliver your application with all the modules compiled, they do not need to be compiled on the user’s machine before they run.

To easily recompile all modules, choose Debug|Compile with the Visual Basic Editor (VBE) active. This command opens and compiles all code in the application, including the code behind forms and reports. It then saves the modules in the compiled state, preserving the compiled state of the application.

Don’tbother choosing the Debug|Compile command if you plan to make additional changes to the application. An application becomes decompiled whenever the application’s controls, forms, reports, or modules are modified. Even something as simple as adding a single control to a form causes the application to lose its compiled state. It therefore is important to choose the Debug|Compile command immediately before you distribute the application.

The process of creating an MDE file compiles all modules, removes editable source code, and compacts the destination database. All Visual Basic code will run, but cannot be viewed or edited. This improves performance, reduces the size of the database, and protects your intellectual property. Memory use also is improved. The process of saving an application as an MDE, and the implications of doing so, are covered in Chapter 27, “Database Security Made Easy,” and Chapter 32, “Distributing Your Application.”

VBA code theoretically can be placed in any module within your application. The problem is that a module is not loaded until a function within it is called. After a single procedure in a module is called, the entire module is loaded into memory. Furthermore, if a single variable within a module is used, the entire module is loaded into memory. As you might imagine, if you design your application without much thought, every module in your application will be loaded.

If you place similar routines all in one module, that module will be loaded, and others will not. This means that if people are using only part of the functionality of your application, they will never be loading other code modules. This conserves memory and therefore optimizes your application.

Because forms are your main interface to your user, making them as efficient as possible can go a long way toward improving the user’s perception of your application’s performance. Additionally, many of the form techniques are extremely easy to implement.

Form-optimization techniques can be categorized in two ways: those that make the forms load more quickly, and those that enable you to more efficiently manipulate objects within the form.

The larger a form and the more controls and objects you have placed on it, the less efficient that form is. Make sure that controls on the form do not overlap. It also is extremely beneficial to group form data onto logical pages. This is especially important if your users have insufficient video RAM. Objects on subsequent pages should not be populated until the user moves to that page.

Forms and their controls should be based on saved queries or embedded SQL statements. Include only fields required by the form in the form’s underlying query. Avoid using Select * queries. Because Access is so efficient at internally optimizing the manipulation of query results, this improves the performance of your forms. To further take advantage of the power of queries, reduce the number of records that the query returns, loading only the records you need to at a particular time.

If you will use a form solely to add new records, set the DataEntry property of the form to Yes so that it opens to a blank record. This is necessary because, otherwise, Access must read all records to display the blank record at the end of the recordset.

Avoid bitmaps and other graphic objects if possible. If you must display an image, it is important to remember that OLE objects take far more resources than images. If an OLE bitmapped object does not need to be changed, convert it to an image. To accomplish this, right-click the object and choose Change To|Image.

Avoid the use of subforms whenever possible. Access treats a subform as a separate form. It therefore takes up significant memory. Make sure that all fields in a subform that are either linked to the main form or used for criteria are indexed. Make sure that only necessary fields are included in the record source of the subform. If the data in the subform does not need to be edited, set its AllowEdits, AllowAdditions, and AllowDeletions properties to No, or set its RecordsetType property to Snapshot.

Make sure that the RowSource for a combo box includes only the columns needed for the combo box. Index on the first field that appears in the combo box. This has a dramatic effect on the speed at which a user can move to an element of the combo box. Also, whenever possible, make the first visible field of a combo box a text field. Access converts numeric fields to text as it searches through the combo box to find a matching value. Finally, don’t base list boxes or combo boxes on linked data if that data rarely, if ever, changes. Instead, make the static table local, updating it whenever necessary.

As a general rule regarding the performance of forms, place all database objects, except data, on each user’s machine. This eliminates the need for Access to constantly pull object definitions over the network.

Close forms that no longer are being used. This is necessary because open forms take up memory and resources, degrading performance.

Another tip that can help you dramatically improve the performance of your forms is to use the default formatting and properties for as many controls as possible. This significantly improves performance because only the form and control properties that differ from the default properties are saved with the form.

Finally, eliminate the code module from forms that don’t need it. A form without a code module loads more quickly and occupies less disk space. You still can call function procedures from an event property using an expression, or you can navigate about your application from the form using hyperlinks. You can remove the module associated with a form by setting the HasModule property to No.

Many of the report-optimization techniques are the same as the form-optimization techniques. Reducing the number of controls, avoiding overlapping controls, basing reports on queries, avoiding OLE objects, and converting unbound object frames that display graphics to image controls are all techniques that improve the performance of reports as well as forms.

You can use a few additional techniques to specifically improve the performance of reports. Eliminate any unnecessary sorting and grouping expressions, and index all fields on which you sort or group. Base subreports on queries rather than on tables, and include only necessary fields in the queries. Make sure that the queries underlying the report are optimized and that you index all fields in the subreport that are linked to the main report.

A special technique that you can use to improve the performance of reports was introduced with Access 95. It involves the No Data event and the HasData property. The No Data event is fired when a report is opened, and no data is returned by the record source of the report. The HasData property is used to determine whether a report is bound to an empty recordset. If the HasData property of a subreport is False, you can hide the subreport, thereby improving performance.