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 find that there are relatively few opportunities for unit testing in my applications. Most of the code that I write interacts with table data or the filing system so is fundamentally hard to unit test. Early on, I tried an approach that may be similar to mocking (spoofing) where I created code that had an optional parameter. If the parameter was used, then the procedure would use the parameter instead of fetching data from the database. It is quite easy to set up a user defined type that has the same field types as a row of data and to pass that to a function. I now have a way of getting test data into the procedure that I want to test. Inside each procedure there was some code that swapped out the real data source for the test data source. This allowed me to use unit testing on a wider variety of function, using my own unit testing functions. Writing unit test is easy, it is just repetitive and boring. In the end, I gave up with unit tests and started using a different approach.

I write in-house applications for myself mainly so I can afford wait till issues find me rather than having to have perfect code. If I do write applications for customers, generally the customer is not fully aware of how much software development costs so I need a low cost way of getting results. Writing unit tests is all about writing a test that pushes bad data at a procedure to see if the procedure can handle it appropriately. Unit tests also confirm that good data is handled appropriately. My current approach is based on writing input validation into every procedure within an application and raising a success flag when the code has completed successfully. Each calling procedure checks for the success flag before using the result. If an issue occurs, it is reported by way of an error message. Each function has a success flag, a return value, an error message, a comment and an origin. A user defined type (fr for function return) contains the data members. Any given function many populate only some of the data members in the user defined type. When a function is run, it usually returns success = true and a return value and sometimes a comment. If a function fails, it returns success = false and an error message. If a chain of functions fails, the error messages are daisy changed but the result is actually a lot more readable that a normal stack trace. The origins are also chained so I know where the issue occurred. The application rarely crashes and accurately reports any issues. The result is a hell of a lot better than standard error handling.

Public Function GetOutputFolder(OutputFolder As eOutputFolder) As  FunctRet

        '///Returns a full path when provided with a target folder alias. e.g. 'temp' folder

            Dim fr As FunctRet

            Select Case OutputFolder
            Case 1
                fr.Rtn = "C:\Temp\"
                fr.Success = True
            Case 2
                fr.Rtn = TrailingSlash(Application.CurrentProject.path)
                fr.Success = True
            Case 3
                fr.EM = "Can't set custom paths – not yet implemented"
            Case Else
                fr.EM = "Unrecognised output destination requested"
            End Select

    exitproc:
        GetOutputFolder = fr

    End Function

Code explained. eOutputFolder is a user defined Enum as below

Public Enum eOutputFolder
    eDefaultDirectory = 1
    eAppPath = 2
    eCustomPath = 3
End Enum

I am using Enum for passing parameters to functions as this creates a limited set of known choices that a function can accept. Enums also provide intellisense when entering parameters into functions. I suppose they provide a rudimentary interface for a function.

'Type FunctRet is used as a generic means of reporting function returns
Public Type  FunctRet
    Success As Long     'Boolean flag for success, boolean not used to avoid nulls
    Rtn As Variant      'Return Value
    EM As String        'Error message
    Cmt As String       'Comments
    Origin As String    'Originating procedure/function
End Type

A user defined type such as a FunctRet also provides code completion which helps. Within the procedure, I usually store internal results to an anonymous internal variable (fr) before assigning the results to the return variable (GetOutputFolder). This makes renaming procedures very easy as only the top and bottom have be changed.

So in summary, I have developed a framework with ms-access that covers all operations that involve VBA. The testing is permanently written into the procedures, rather than a development time unit test. In practice, the code still runs very fast. I am very careful to optimise lower level functions that can be called ten thousand times a minute. Furthermore, I can use the code in production as it is being developed. If an error occurs, it is user friendly and the source and reason for the error are usually obvious. Errors are reported from the calling form, not from some module in the business layer, which is an important principal of application design. Furthermore, I don't have the burden of maintaining unit testing code, which is really important when I am evolving a design rather than coding a clearly conceptualised design.

There are some potential issues. The testing is not automated and new bad code is only detected when the application is run. The code does not look like standard VBA code (it is usually shorter). Still, the approach has some advantages. It is far better that using an error handler just to log an error as the users will usually contact me and give me a meaningful error message. It can also handle procedures that work with external data. JavaScript reminds me of VBA, I wonder why JavaScript is the land of frameworks and VBA in ms-access is not.

A few days after writing this post, I found an article on The CodeProject that comes close to what I have written above. The article compares and contrasts exception handling and error handling. What I have suggested above is akin to exception handling.

Answer 2

Data Access Pages have been deprecated by MS for quite some time, and never really worked in the first place (they were dependent on the Office Widgets being installed, and worked only in IE, and only badly then).

It is true that Access controls that can get focus only have a window handle when they have the focus (and those that can't get focus, such as labels, never have a window handle at all). This makes Access singularly inappropriate to window handle-driven testing regimes.

Indeed, I question why you want to do this kind of testing in Access. It sounds to me like your basic Extreme Programming dogma, and not all of the principles and practices of XP can be adapted to work with Access applications -- square peg, round hole.

So, step back and ask yourself what you're trying to accomplish and consider that you may need to utilize completely different methods than those that are based on the approaches that just can't work in Access.

Or whether that kind of automated testing is valid at all or even useful with an Access application.

Answer 3

There are good suggestions here, but I'm surprised no one mentioned centralized error processing. You can get addins that allow for quick function/sub templating and for adding line numbers (I use MZ-tools). Then send all errors to a single function where you can log them. You can also then break on all errors by setting a single break point.

Answer 4

Although that being a very old answer:

There is AccUnit, a specialized Unit-Test framework for Microsoft Access.

Answer 5

Access is a COM application. Use COM, not Windows API. to test things in Access.

The best Test environment for an Access Application is Access. All of your Forms/Reports/Tables/Code/Queries are available, there is a scripting language similar to MS Test (Ok, you probably don't remember MS Test), there is database environment for holding your test scripts and test results, and the skills you build here are transferable to your application.

Answer 6

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!