Best way to test a MS Access application?

Question

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?

Answer 1

I would design the application to have as much work as possible done in queries and in vba subroutines so that your testing could be made up of populating test databases, running sets of the production queries and vba against those databases and then looking at the output and comparing to make sure the output is good. This approach doesn't test the GUI obviously, so you could augment the testing with a series of test scripts (here I mean like a word document that says open form 1, and click control 1) that are manually executed.

It depends on the scope of the project as the level of automation necessary for the testing aspect.

Answer 2

If your interested in testing your Access application at a more granular level specifically the VBA code itself then VB Lite Unit is a great unit testing framework for that purpose.

Answer 3

I have not tried this, but you could attempt to publish your access forms as data access web pages to something like sharepoint or just as web pages and then use an tool such as selenium to drive the browser with a suite of tests.

Obviously this is not as ideal as driving the code directly through unit tests, but it may get you part of the way. good luck

Answer 4

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.

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.

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 = value
End Property

Public Property Get Text() As String
    Text = Me.TextBox1.value
End Property

Private Sub SayHello_Click()
    RaiseEvent OnSayHello
End Sub

Private Sub TextBox1_AfterUpdate()
    RaiseEvent AfterTextUpdate
End 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 String
Public Property Let Text(value As String)
    mText = value
End Property

Public Property Get Text() As String
    Text = mText
End Property

Public 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 = result
End Function

Public 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_Form1
Private model As MyModel

Public Sub Run()
    Set model = New MyModel
    Set view = New Form_Form1
    view.Visible = True
End Sub

Private Sub view_AfterTextUpdate()
    model.Text = view.Text
End Sub

Private 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 FormController

Public Sub Run()
    Set controller = New FormController
    controller.Run
End 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 Unit I can't say I've personally used it. It's out there, but hasn't seen an update since 2005.

3. xlUnit xlUnit 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.)

'@TestModule
Private Assert As New Rubberduck.AssertClass

'@TestMethod
Public Sub ReversedReversesCorrectly()

Arrange:
    Dim model As New MyModel
    Const original As String = "Hello"
    Const expected As String = "olleH"
    Dim actual As String

    model.Text = original

Act:
    actual = model.Reversed

Assert:
    Assert.AreEqual expected, actual

End 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.

Answer 5

I've taken a page out of Python's doctest concept and implemented a DocTests procedure in Access VBA. This is obviously not a full-blown unit-testing solution. It's still relatively young, so I doubt I've worked out all the bugs, but I think it's mature enough to release into the wild.

Just copy the following code into a standard code module and press F5 inside the Sub to see it in action:

'>>> 1 + 1
'2
'>>> 3 - 1
'0
Sub DocTests()
Dim Comp As Object, i As Long, CM As Object
Dim Expr As String, ExpectedResult As Variant, TestsPassed As Long, TestsFailed As Long
Dim Evaluation As Variant
    For Each Comp In Application.VBE.ActiveVBProject.VBComponents
        Set CM = Comp.CodeModule
        For i = 1 To CM.CountOfLines
            If Left(Trim(CM.Lines(i, 1)), 4) = "'>>>" Then
                Expr = Trim(Mid(CM.Lines(i, 1), 5))
                On Error Resume Next
                Evaluation = Eval(Expr)
                If Err.Number = 2425 And Comp.Type <> 1 Then
                    'The expression you entered has a function name that ''  can't find.
                    'This is not surprising because we are not in a standard code module (Comp.Type <> 1).
                    'So we will just ignore it.
                    GoTo NextLine
                ElseIf Err.Number <> 0 Then
                    Debug.Print Err.Number, Err.Description, Expr
                    GoTo NextLine
                End If
                On Error GoTo 0
                ExpectedResult = Trim(Mid(CM.Lines(i + 1, 1), InStr(CM.Lines(i + 1, 1), "'") + 1))
                Select Case ExpectedResult
                Case "True": ExpectedResult = True
                Case "False": ExpectedResult = False
                Case "Null": ExpectedResult = Null
                End Select
                Select Case TypeName(Evaluation)
                Case "Long", "Integer", "Short", "Byte", "Single", "Double", "Decimal", "Currency"
                    ExpectedResult = Eval(ExpectedResult)
                Case "Date"
                    If IsDate(ExpectedResult) Then ExpectedResult = CDate(ExpectedResult)
                End Select
                If (Evaluation = ExpectedResult) Then
                    TestsPassed = TestsPassed + 1
                ElseIf (IsNull(Evaluation) And IsNull(ExpectedResult)) Then
                    TestsPassed = TestsPassed + 1
                Else
                    Debug.Print Comp.Name; ": "; Expr; " evaluates to: "; Evaluation; " Expected: "; ExpectedResult
                    TestsFailed = TestsFailed + 1
                End If
            End If
NextLine:
        Next i
    Next Comp
    Debug.Print "Tests passed: "; TestsPassed; " of "; TestsPassed + TestsFailed
End Sub

Copying, pasting, and running the above code from a module named Module1 yields:

Module: 3 - 1 evaluates to:  2  Expected:  0 
Tests passed:  1  of  2

A few quick notes:

• It has no dependencies (when used from within Access)
• It makes use of Eval which is a function in the Access.Application object model; this means you could use it outside of Access but it would require creating an Access.Application object and fully qualifying the Eval calls
• There are some idiosyncrasies associated with Eval to be aware of
• It can only be used on functions that return a result that fits on a single line

Despite its limitations, I still think it provides quite a bit of bang for your buck.

Edit: Here is a simple function with "doctest rules" the function must satisfy.

Public Function AddTwoValues(ByVal p1 As Variant, _
        ByVal p2 As Variant) As Variant
'>>> AddTwoValues(1,1)
'2
'>>> AddTwoValues(1,1) = 1
'False
'>>> AddTwoValues(1,Null)
'Null
'>>> IsError(AddTwoValues(1,"foo"))
'True

On Error GoTo ErrorHandler

    AddTwoValues = p1 + p2

ExitHere:
    On Error GoTo 0
    Exit Function

ErrorHandler:
    AddTwoValues = CVErr(Err.Number)
    GoTo ExitHere
End Function

Answer 6

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.