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 DoubleIf FirstNum < SecondNum Then    GetLowestNumber = FirstNumElse    GetLowestNumber = SecondNumEnd IfEnd 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.

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.

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 userstest 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!