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?
Microsoft doesn't have any plans to replace Microsoft Access while also planning to remove the application from Office 365. Therefore, Access users will need to look at alternative systems to run their desktop databases, such as LibreOffice Base, Zoho Creator, or Bubble.
It is well suited, proven and highly relevant even in 2021. We have several reasons to say that Microsoft Access is still a truly relevant Database system even in 2021, in fact many more years to come.
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.
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.
AccUnit
VB Lite Unit I can't say I've personally used it. It's out there, but hasn't seen an update since 2005.
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.
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.
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.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With