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

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 3

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.

--
David W. Fenton
http://dfenton.com/DFA/

Answer 4

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 5

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 tendancy 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 incuding 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 normalisation (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 normalisation, even with MS Access, is a long process. But it is really worth the pain!

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.

Answer 7

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 8

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.