Best way to test a MS Access application? Best way to test a MS Access application? vba vba

Best way to test a MS Access application?


1. Write Testable Code

First, stop writing business logic into your Form's code behind. That's not the place for it. It can't be properly tested there. In fact, you really shouldn't have to test your form itself at all. It should be a dead dumb simple view that responds to User Interaction and then delegates responsibility for responding to those actions to another class that is testable.

How do you do that? Familiarizing yourself with the Model-View-Controller pattern is a good start.

Model View Controller diagram

It can't be done perfectly in VBA due to the fact that we get either events or interfaces, never both, but you can get pretty close. Consider this simple form that has a text box and a button.

simple form with text box and button

In the form's code behind, we'll wrap the TextBox's value in a public property and re-raise any events we're interested in.

Public Event OnSayHello()Public Event AfterTextUpdate()Public Property Let Text(value As String)    Me.TextBox1.value = valueEnd PropertyPublic Property Get Text() As String    Text = Me.TextBox1.valueEnd PropertyPrivate Sub SayHello_Click()    RaiseEvent OnSayHelloEnd SubPrivate Sub TextBox1_AfterUpdate()    RaiseEvent AfterTextUpdateEnd Sub

Now we need a model to work with. Here I've created a new class module named MyModel. Here lies the code we'll put under test. Note that it naturally shares a similar structure as our view.

Private mText As StringPublic Property Let Text(value As String)    mText = valueEnd PropertyPublic Property Get Text() As String    Text = mTextEnd PropertyPublic Function Reversed() As String    Dim result As String    Dim length As Long    length = Len(mText)    Dim i As Long    For i = 0 To length - 1        result = result + Mid(mText, (length - i), 1)    Next i    Reversed = resultEnd FunctionPublic Sub SayHello()    MsgBox Reversed()End Sub

Finally, our controller wires it all together. The controller listens for form events and communicates changes to the model and triggers the model's routines.

Private WithEvents view As Form_Form1Private model As MyModelPublic Sub Run()    Set model = New MyModel    Set view = New Form_Form1    view.Visible = TrueEnd SubPrivate Sub view_AfterTextUpdate()    model.Text = view.TextEnd SubPrivate Sub view_OnSayHello()    model.SayHello    view.Text = model.Reversed()End Sub

Now this code can be run from any other module. For the purposes of this example, I've used a standard module. I highly encourage you to build this yourself using the code I've provided and see it function.

Private controller As FormControllerPublic Sub Run()    Set controller = New FormController    controller.RunEnd Sub

So, that's great and all but what does it have to do with testing?! Friend, it has everything to do with testing. What we've done is make our code testable. In the example I've provided, there is no reason what-so-ever to even try to test the GUI. The only thing we really need to test is the model. That's where all of the real logic is.

So, on to step two.

2. Choose a Unit Testing Framework

There aren't a lot of options here. Most frameworks require installing COM Add-ins, lots of boiler plate, weird syntax, writing tests as comments, etc. That's why I got involved in building one myself, so this part of my answer isn't impartial, but I'll try to give a fair summary of what's available.

  1. AccUnit

    • Works only in Access.
    • Requires you to write tests as a strange hybrid of comments and code. (no intellisense for the comment part.
    • There is a graphical interface to help you write those strange looking tests though.
    • The project has not seen any updates since 2013.
  2. VB Lite UnitI can't say I've personally used it. It's out there, but hasn't seen an update since 2005.

  3. xlUnitxlUnit isn't awful, but it's not good either. It's clunky and there's lots of boiler plate code. It's the best of the worst, but it doesn't work in Access. So, that's out.

  4. Build your own framework

    I've been there and done that. It's probably more than most people want to get into, but it is completely possible to build a Unit Testing framework in Native VBA code.

  5. Rubberduck VBE Add-In's Unit Testing Framework
    Disclaimer: I'm one of the co-devs.

    I'm biased, but this is by far my favorite of the bunch.

    • Little to no boiler plate code.
    • Intellisense is available.
    • The project is active.
    • More documentation than most of these projects.
    • It works in most of the major office applications, not just Access.
    • It is, unfortunately, a COM Add-In, so it has to be installed onto your machine.

3. Start writing tests

So, back to our code from section 1. The only code that we really needed to test was the MyModel.Reversed() function. So, let's take a look at what that test could look like. (Example given uses Rubberduck, but it's a simple test and could translate into the framework of your choice.)

'@TestModulePrivate Assert As New Rubberduck.AssertClass'@TestMethodPublic Sub ReversedReversesCorrectly()Arrange:    Dim model As New MyModel    Const original As String = "Hello"    Const expected As String = "olleH"    Dim actual As String    model.Text = originalAct:    actual = model.ReversedAssert:    Assert.AreEqual expected, actualEnd Sub

Guidelines for Writing Good Tests

  1. Only test one thing at a time.
  2. Good tests only fail when there is a bug introduced into the system or the requirements have changed.
  3. Don't include external dependencies such as databases and file systems. These external dependencies can make tests fail for reasons outside of your control. Secondly, they slow your tests down. If your tests are slow, you won't run them.
  4. Use test names that describe what the test is testing. Don't worry if it gets long. It's most important that it is descriptive.

I know that answer was a little long, and late, but hopefully it helps some people get started in writing unit tests for their VBA code.


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!


Another advantage of Access being a COM application is that you can create an .NET application to run and test an Access application via Automation. The advantage of this is that then you can use a more powerful testing framework such as NUnit to write automated assert tests against an Access app.

Therefore, if you are proficient in either C# or VB.NET combined with something like NUnit then you can more easily create greater test coverage for your Access app.