Unit testing in MS Access

Question

I have an Access database project that is getting a bit to large to try to keep track of in my head. I know it is a little late in the game, but better late than never right?

I have Googled but found nothing to my satisfaction. Is there any such thing as a unit testing framework for MS VBA?

Procryptic

I'm a lowly Access developer so perhaps unable to appreciate the joys of unit testing, but it always seems to me that errors in my apps pop up in things I didn't think to test. So, you're always going to be in the same epistemological quandary, i.e., designing tests that test the things you haven't anticipated testing. I know the whole field of unit testing is supposed to address that, but the problem is it's a poor fit with the Access/VBA model, as user behavior at runtime is inherently unpredictable (and unscriptable). – Jewett

@David-W-Fenton: that is the philosophy I have been living under up to this point. I thinking that I may have to retain that philosophy for very similar reasons to what you mentioned. – Procryptic

To add to David's comments I let my users do the unit testing. <tonque somewhat in cheek> The reality is though is that someone else should be doing final testnig. And they should be a slightly twisted, demented user. Side note. MS seems to have a ratio of about one program manager to one developer to one tester. I've had some interesting chats with a few of the testers. – Incontrollable

Answer 1

There is VBAUnit for free on SourceForge. Also, the commercial vbUnit 3 may support VBA as well as regular VB ... I'm not sure, though.

Internationalize

Answer 2

I do some fairly extensive development in Microsoft Access, and have found AccUnit to meet my needs quite nicely. The author's site is in German (but includes some English translation) but once you get the basic concepts it makes unit testing very easy to implement.

AccUnit uses SimplyVBUnit 3.0 on the back end to power the unit testing, but I find the AccUnit VBA add-in approach simpler to implement in my projects.

Installation

The installation process is described on the author's site, but essentially includes the following two items:

• Install SimplyVBUnit 3.0 (note that this is no longer available on SourceForge, but a copy is available on the AccUnit site.)
• Install the AccUnit add-in downloaded from the link above.

Basic Usage

After installing, you will find a new sub-menu under the Tools menu. Click AccUnit -> New Test -> Test Class with AccUnit-Features to add a new testing class module to your project.

You can name the module anything you want, so this will depend a little on how you want to do the testing. Some people prefer a test class for every module, to keep things organized, while others keep things simple in smaller projects by using a single class for unit tests.

The sample code is a little cryptic, but the basic concept is that when you run tests, it is going to sequentially run all the Subs in the class, and display the test results. Let's make a couple sample tests.

Simple Example

Let's say we have a function (in a standard module) that looks like this:

Public Function GetLowestNumber(FirstNum As Double, SecondNum As Double) As Double
If FirstNum < SecondNum Then
    GetLowestNumber = FirstNum
Else
    GetLowestNumber = SecondNum
End If
End Function

Back in the AccUnit test class, we could now make a simple test to verify that this function indeed returns the lowest number.

Public Sub basUtility_GetLowestNumber_Test1()
    Assert.That GetLowestNumber(3, 4), Iz.EqualTo(3)
End Sub

Now, on the AccUnit toolbar, click the button to Run all tests. The test window will then appear and show the results of the test. (It should pass, if you copied in the above code.) :-)

Designing a Row Test

But what makes this tool very powerful is the ability to run a test using various combinations of criteria. In AccUnit they call this a Row Test. To use it, simply adjust the function to accept parameters and list your test cases in comment lines above the function.

' AccUnit:Row(3,4,3).Name = "Smaller first"
' AccUnit:Row(4,3,3).Name = "Larger first"
' AccUnit:Row(3,3,3).Name = "Equal"
' AccUnit:Row(3.002,3.6,3.003).Name = "Decimals"
' AccUnit:Row(-3,3,-3).Name = "Mixed sign"
' AccUnit:Row(-7,-3,-7).Name = "Negative"
Public Sub basUtility_GetLowestNumber_RowTest(Num1 As Double, Num2 As Double, Expected As Double)
    Assert.That GetLowestNumber(Num1, Num2), Iz.EqualTo(Expected)
End Sub

Now click Run all tests again, and you will see the results listed.

Notice that the results of the individual row tests are nested under the parent test, and the friendly names we gave them are shown for easy reference.

Also note that one of these tests failed. (In this case it was an intentional error in the row test expected result, just to demonstrate what a failed test looks like.) Double-clicking the failed test jumps to the code where I can debug things further.

Summary

I have just shared a couple brief examples here, but there are a number of additional tools and features to explore in AccUnit. (Test tags, ignore flag, code templates, etc...)

Hopefully this gives you a bit of a flavor of this particular Unit Testing option for Microsoft Access (or other VBA products). There are other good options out there, but for me, this provided the flexibility and simplicity I was looking for.

Blackford

Answer 3

Personally I think with a combination of the excellent access FE auto updater

http://autofeupdater.com/

And a UAT environment that is either linked into the live data or testing data that should catch most of the problems. I’m also a sole access developer and my model consists of

• dev land where I make changes

• UAT land where a select few users test the changes and approve

• Live land where everyone else lives

The key difference I see between projects is the capability of the people doing the UAT. I had one major release which was migrating from an access back end to SQL server, I sent the email saying that the version was in UAT ready for testing and got one back 30 minutes later saying it had all been tested (impossible) low and behold there was a bug in a very common area.

So in conclusion, set your staged environment up and pick your testers well!

Supercargo

I use Auto FE. Thanks for the UAT suggestions. – Procryptic

Answer 4

Not that I'm aware of, but you could use a code review instead.

Awake

Not a bad idea except for the fact that I am the sole developer on the project! – Procryptic

What do you want to test? You can let your users do that for you, too. – Awake

That is how it is working right now. Basically what it means is that I release an update and the users tell me if there is anything wrong. This works but...when the same issue keeps happening, it makes sense to be able to automate the test instead of waiting for the users to complain. Maybe I should clarify that this is a custom application for only one company. – Procryptic

BTW if I feel the need I will "publish" an update every hour or two or although typically every day or two. But if there is an important bug then I will fix just that and get out the update. See Ultra Frequent Application Deployment granite.ab.ca/access/ufad.htm – Incontrollable

Answer 5

I use the unit testing framework found in RubberDuckVBA

It's full featured and it's just one tool in the toolbox that is the duck.

Looks like this:

Hermeneutics