Microsoft Access Performance - Speed up your Access Forms

(Last updated 2007/07/07)

 By Mark Plumpton, Custom Data Solutions

I have a form that took 85 seconds to save. After applying the technique below it reduced the save time to just a couple of seconds. It also reduced my compacted database size from 5Meg to 4Meg after applying this technique.

This is the tip: Load the form, subform, combobox and listbox record sources at run-time. That's it. You will achieve dramatic performance improvement and reduced database size.

Test Results for Access 2000

Dimensions:
Computer Pentium 4, 1.6GHz, 256Mb
Software WinXP, Access 2000 SR-1
Large Table Size approx 3700Kb in linked database (incl 450Kb of indexes)
Fields in Rowsource 192 from table + 19 calculated
Records 2330
Controls On Form 288 (excluding labels)
Combos On Form 24 (7 of these had GroupBy queries based on the same large table)
Sub-forms 3

Here's the technique: Delete the SQL from the RecordSource and RowSource properties of the form, subforms, comboboxes and listboxes. Now in the Form_Load event load the appropriate SQL as follows ...

Private Sub Form_Load()
    Me.RecordSource = "qryLargeTable"
    Me.txtSomeField.RowSource = _
    "SELECT SomeField " & _
    "FROM qryLargeTable " & _
    "GROUP BY SomeField " & _
    "ORDER BY SomeField;"
End Sub

It also pays to clear the record sources in the Unload event as sometime these get saved with the form in Access 2000.

Private Sub Form_Unload(Cancel As Integer)
    Me.RecordSource = ""
    Me.cboFindRecord.RowSource = ""
End Sub

A more advanced way is to store the SQL in the Tag property of each combobox control and subform form. Then you can use the following code...

Private Sub Form_Load()
Dim ctl As Control

Me.RecordSource = "qryLargeTable"

For Each ctl In Me.Controls
    Select Case ctl.Properties("ControlType")
    Case acComboBox, acListBox
        ctl.RowSource = ctl.Tag
    Case acSubform
        ctl.Form.RecordSource = ctl.Form.Tag
    Case Else
        'do nothing
    End Select
    Next ctl

    Set ctl = Nothing
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Dim ctl As Control

    Me.RecordSource = ""

    For Each ctl In Me.Controls
        Select Case ctl.Properties("ControlType")
        Case acComboBox, acListBox
            ctl.RowSource = ""
        Case acSubform
            ctl.Form.RecordSource = ""
        Case Else
            'do nothing
        End Select
    Next ctl

    Set ctl = Nothing
End Sub

[ Access | Main ]