Best way to test a MS Access application?

The Problem (Q-score 32, ranked #12th of 95 in the VBA Core archive)

The scenario as originally posted in 2008

With the code, forms and data inside the same database I am wondering what are the best practices to design a suite of tests for a Microsoft Access application (say for Access 2007).

One of the main issues with testing forms is that only a few controls have a hwnd handle and other controls only get one they have focus, which makes automation quite opaque since you cant get a list of controls on a form to act on.

Any experience to share?

Why community consensus is tight on this one

Across 95 VBA Core entries in the archive, the accepted answer here holds solid answer (above median) status — meaning voters are unusually aligned on the right fix.

The Verified Solution — solid answer (above median) (+17)

7-line VBA Core pattern (copy-ready)

I appreciated knox’s and david’s answers. My answer will be somewhere between theirs: just make forms that do not need to be debugged!

I think that forms should be exclusively used as what they are basically, meaning graphic interface only, meaning here that they do not have to be debugged! The debugging job is then limited to your VBA modules and objects, which is a lot easier to handle.

There is of course a natural tendency to add VBA code to forms and/or controls, specially when Access offers you these great “after Update” and “on change” events, but I definitely advise you not to put any form or control specific code in the form’s module. This makes further maintenance and upgrade very costy, where your code is split between VBA modules and forms/controls modules.

This does not mean you cannot use anymore this AfterUpdate event! Just put standard code in the event, like this:

Private Sub myControl_AfterUpdate()  
    CTLAfterUpdate myControl
    On Error Resume Next
    Eval ("CTLAfterUpdate_MyForm()")
    On Error GoTo 0  
End sub

Where:

• CTLAfterUpdate is a standard procedure run each time a control is updated in a form

• CTLAfterUpdateMyForm is a specific procedure run each time a control is updated on MyForm

I have then 2 modules. The first one is

• utilityFormEvents
  where I will have my CTLAfterUpdate generic event

The second one is

• MyAppFormEvents
  containing the specific code of all specific forms of the MyApp application
  and including the CTLAfterUpdateMyForm procedure. Of course, CTLAfterUpdateMyForm
  might not exist if there are no specific code to run. This is why we turn the
  “On error” to “resume next” …

Choosing such a generic solution means a lot. It means you are reaching a high level of code normalization (meaning painless maintenance of code). And when you say that you do not have any form-specific code, it also means that form modules are fully standardized, and their production can be automated: just say which events you want to manage at the form/control level, and define your generic/specific procedures terminology.
Write your automation code, once for all.
It takes a few days of work but it give exciting results. I have been using this solution for the last 2 years and it is clearly the right one: my forms are fully and automatically created from scratch with a “Forms Table”, linked to a “Controls Table”.
I can then spend my time working on the specific procedures of the form, if any.

Code normalization, even with MS Access, is a long process. But it is really worth the pain!

When to Use It — vintage (14+ years old, pre-2013)

Ranked #12th in its category — specialized fit

This pattern sits in the 86% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the VBA Core archive for a higher-consensus alternative.

What changed between 2008 and 2026

The answer is 18 years old. The VBA Core object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.