How to: Test an application

Check an Access application with tests written in VBA and run them from the command line or a CI server, without anybody typing or clicking on the desktop.

• The project file names a test function (<Test>) that returns the names of the test procedures.
• A test procedure is a public Sub without arguments in a standard module of the sources. It drives the application in code: it opens forms with DoCmd.OpenForm, calls the commands of the application, reads controls and data, and checks the results with WklTest.Check.
• The application lets the tests answer its dialogs: before each MsgBox, InputBox or file dialog it asks WklTest.Answer. Outside a test run the answer is Empty and the dialog appears as before.
• wkl-office test builds the project, then runs each test procedure on its own in a hidden Access, on a copy of the database. It prints one line per check and ends with an exit code; the work folder holds a JUnit report.
• PowerShell starts and evaluates, VBA drives. A script or CI server calls wkl-office test and wkl-office call and reads the exit code, the JSON output and the reports. There is no interface through which a script drives a running Access over COM (open a form, trigger a command, read a control); the test procedures do that in VBA.

The SDK puts the module WklTest into every build of a project that names a test function. The application gets no new reference.

1. Name the test function in the project file.

<Project>
  <PropertyGroup>
    <SchemaVersion>2</SchemaVersion>
    <AssemblyName>Orders</AssemblyName>
    <Test>AllTests</Test>
  </PropertyGroup>
</Project>

2. Let the application ask through WklTest.Answer. One function per kind of dialog is enough; the rest of the application calls it instead of MsgBox:

' A question to the user; in a test run the answer the test expects instead of the message box.
Public Function AskUser(ByVal Prompt As String, ByVal Buttons As VbMsgBoxStyle) As VbMsgBoxResult
    Dim reply As Variant
 
    reply = WklTest.Answer(WklTest.KindMessage, Prompt)
    If IsEmpty(reply) Then
        AskUser = MsgBox(Prompt, Buttons)
    ElseIf IsNumeric(reply) Then
        AskUser = CLng(reply)
    Else
        AskUser = vbCancel
    End If
End Function

3. Write the test procedures in a standard module of the sources, for example source\modules\basTests.bas:

Option Compare Database
Option Explicit
 
' The test procedures wkl-office test runs, in this order.
Public Function AllTests() As Variant
    AllTests = Array("Orders_Total", "Orders_DeleteAsks")
End Function
 
Public Sub Orders_Total()
    WklTest.Check "total of two lines", OrderTotal(Array(10, 5)) = 15, "total was " & OrderTotal(Array(10, 5))
End Sub
 
Public Sub Orders_DeleteAsks()
    WklTest.Expect WklTest.KindMessage, "Delete order *", vbNo
    DeleteOrder 42
    WklTest.Check "order 42 kept after No", DCount("*", "tblOrder", "OrderId = 42") = 1, "order 42 was deleted"
End Sub

A test procedure needs no error handler: a run-time error it does not handle is reported as a failed line with the text of the error, and the next procedure runs.

4. Run the tests.

wkl-office test Orders.acproj
wkl-office test Orders.acproj --only Orders_DeleteAsks
wkl-office test Orders.acproj -c Release -t accde --json

OK   Orders_Total: total of two lines
FAIL Orders_DeleteAsks: order 42 kept after No :: order 42 was deleted
1 OK, 1 FAIL (2 test procedures)

Exit code 0 means every check passed, 15 that a check failed. The other codes and the JSON output are listed in the command-line reference.

While the database is open in Access, the build cannot replace it; --no-build then tests the database built last, here in the terminal of the add-in:

A test run with two test procedures: one line per check, then the summary.

In the VBA editor, the menu Tests of the add-in runs the same test procedures on the database open in Access, without leaving the editor; a failed check opens its test procedure from Problems (see How to: Use the Access add-in).

With --json, wkl-office test prints the result as one JSON object (fields: JSON output); the exit code tells how the run ended. The following script is a CI step in the project folder: it tests the ACCDB and the ACCDE, puts the input files from fixtures into the work folder before each run, prints the failed checks and ends with the first exit code that is not 0.

# Runs the tests of a project as a CI step: ACCDB and ACCDE, input files in the work folder, failed checks listed.
param([string] $Project = 'Orders.acproj', [string] $Results = 'test-results')
 
$worst = 0
foreach ($target in 'accdb', 'accde') {
    $work = Join-Path $Results $target
    New-Item -ItemType Directory $work -Force | Out-Null
    Copy-Item fixtures\* $work -Recurse -Force    # the tests read them from CurrentProject.Path
 
    $run = wkl-office test $Project -t $target --work $work --json | ConvertFrom-Json
    $code = $LASTEXITCODE
    switch ($code) {
        0       { "${target}: $($run.passed) checks passed" }
        15      { "${target}: $($run.failed) checks failed"
                  $run.checks | Where-Object { -not $_.ok } | ForEach-Object { "  FAIL $($_.test): $($_.name) :: $($_.message)" } }
        13      { "${target}: time limit: $($run.problem)" }
        16      { "${target}: Access ended: $($run.problem)" }
        default { "${target}: no test run (exit $code): $($run.problem)" }
    }
    if ($code -ne 0 -and $worst -eq 0) { $worst = $code }
}
exit $worst

Afterwards each work folder holds the copy of the database, wkl-test-run.jsonl and the JUnit report wkl-test-report.xml; the CI server archives the folder test-results. When a run stopped (exit code 13 or 16), the checks written up to then are in wkl-test-run.jsonl, one JSON line per entry; checks have kind equal to check:

Get-Content test-results\accdb\wkl-test-run.jsonl | ForEach-Object { $_ | ConvertFrom-Json } |
    Where-Object { $_.kind -eq 'check' -and -not $_.ok } | Format-Table test, name, message

Behavior that applies only outside a test run (WklTest.InRun() is False) is checked with call in the same work folder. call replaces the copy of the database there with a new copy of the build result; the other files stay, and {work} in an argument stands for the work folder:

$text = wkl-office call Orders.acproj OrderNumberText 42 --work test-results\accdb
if ($LASTEXITCODE -ne 0 -or $text -ne 'A-00042') { throw "OrderNumberText returned '$text' (exit $LASTEXITCODE)" }

The SDK owns the module: every build replaces a module WklTest of the sources with its own, and the next export writes it into the sources. Change it only through a new version of the SDK. An application that wants to compile before it is built with the SDK can keep a placeholder module WklTest with Answer (returns Empty), InRun (returns False), the constants KindMessage to KindFolder with the values of the table, and Cancel (returns Null) when the code uses it.

• Names of the module. WklTest brings public names into the project: InRun, Cancel, Answer, Expect, Check, Progress and the constants KindMessage to KindFolder. When a standard module of the application has a public member of the same name and the code uses it without its module, VBA reports “Ambiguous name detected” and the project no longer compiles. Name the module: WklTest.KindOpen for the SDK, <module>.KindOpen for the application's own; the examples on this page always write WklTest..
• Nothing starts by itself. The copy is opened without its start form, ribbon callbacks and AutoExec macro, as with call; a database with an AutoExec macro is refused. A test that needs the setup the start form does calls that code itself.
• One procedure at a time. The variables of the modules keep their values from one procedure to the next, but a run-time error resets them; do not let a test depend on an earlier one.
• Dialogs that do not ask WklTest.Answer are answered with Cancel, No or OK, and the procedure fails. A form opened with acDialog waits until the time limit; open such forms without acDialog in a test and check them directly. A run-time error dialog of VBA appears on the desktop for about a second, also when Access is hidden.
• Time limit and crashes. A procedure that passes --timeout (default 120 seconds) ends its Access, and the run stops with exit code 13. When Access ends during a procedure, the run stops with exit code 16 and names the last line written; the lines before are in wkl-test-run.jsonl in the work folder.
• Tests that stop running are noticed: a run of all procedures with fewer checks than the last green run fails with the line check count.
• Tests per language. --language sl runs the procedures with the application texts and the WKL components in Slovenian, whatever language Office uses, so one machine checks every language of the application (texts of an application). The count of checks is kept per language. It needs WKL Forms Object Library 1.65 or later.
• CI servers read build\<configuration>\test\wkl-test-report.xml (JUnit XML). The tests run in the Access of the build machine, in its bitness.
• The work folder (default build\<configuration>\test, otherwise --work) is created when it is missing but not emptied: test replaces only the copy of the database and wkl-test-run.jsonl in it. Files a script puts there beforehand are found by the tests under CurrentProject.Path.
• Values for the tests. test passes no parameters to the test procedures; put inputs into the work folder as files. On Windows 11 the hidden Access does see the environment variables of the calling process, but the SDK does not guarantee it. Do not write passwords into the work folder: its files stay there after the run.
• Access Runtime and operation by hand. The tests run in full Access, also with -t accde; a run under Access Runtime (/runtime) is not supported. Operation with real keys and mouse in a visible Access (tab order, focus, shortcut keys, drag and drop) is not checked by test.
• Tests in the delivered application. The test procedures are sources like every other module and are built into every database of the project, also the delivered one. To keep them out of the delivery, put them into a test project of their own that names the main project as its template (project folder).

• Command-line reference: test and call
• Project file: test function
• Project folder