Authored by Ben Clothier
If you program for a a living with Microsoft Access you are going to use objects through out your code. Objects are a more complex entity than a regular numeric or string variable, having properties and in some cases events associated with them that regular variables do not. It’s considered good practice to destroy objects at the end of your subroutines in order to free up memory and resources. (Access is supposed to do this automatically but just to be safe you should get into the habit of doing so). One pattern that irked me was the repeated creation & destruction of objects in each routine since there are set of common objects we refer to again and again. The first inclination may be to use public variables, but the problem is that there’s no way to ensure that the object is properly initialized. For example:
Private con As New ADODB.Connection
Initializing the variable con above doesn’t necessarily mean it’s ready for our use. We typically want it to be opened using a specific connection string, a certain provider and define other properties. That still leaves too much work to the routine calling the variable con. One possible solution is to use a class module but now we’re creating entirely new objects that have to be invoked, taking us a step back instead of forward. For the readers who are fluent in other programming languages, what VBA is basically missing is a constructor. But not to fear – we can get something very close to it.
Okay, so how do I get the object in a certain state at all times?
The short answer is to create a static variable inside a procedure and return that variable — a pattern that some Access veterans have come to call ‘self-healing objects’. Normally all variables cease to exist after a procedure has terminated, but using the keyword Static prevents it from happening, allowing you to pickup where you left off last time. Here’s a simple example using FileSystemObject (I’ll get to Connection object shortly)
Public Property Get FSO() AS Object
Static f As Object
If f Is Nothing Then
Set f = CreateObject("WScript.FileSystemObject")
Set FSO = f
The beauty here is that we no longer need to wonder whether our object “FSO” is initialized. On the very first call, and it doesn’t matter which routine happened to ask for FSO first, FSO will be created just in time, and if a routine decides to destroy FSO, the next routine that calls FSO will re-initialize it.
So, it’s just like the New keyword?
Yes, but it’s actually better because we can do more than ensuring that the object is created. We’ll go to the example of using a ADODB.Connection:
Public Property Get MyConnection() As ADODB.Connection
Static c As ADODB.Connection
Select Case True
Case c Is Nothing, c.State = adStateClosed
Set c = New ADODB.Connection
.ConnectionString = "..."
.CursorLocation = ...
.CommandTimeOut = ...
Set MyConnection = c
The procedure MyConnection will serve as our connection object in our code as opposed to using a single object. It’s a single location where we define all our connection parameters and not worry about it again whenever we invoke it. This helps enormously with maintaining the codebase – who likes changing 100s of individual routines to update the connection parameters? Didn’t think so. 🙂
There’s another big advantage to using self-healing objects specifically for developers – because it’s self-healing, it doesn’t mean that it’ll go boom if you’ve had to press that “Stop” button in a middle of debug session. It will reset to nothing but on the next call, will get initialized just in time for you.
Some of you may be wondering about Select Case. When we use “Select Case True”, we get what is called “lazy evaluation”. For those unfamiliar with the concept, Consider this:
If 1 = 1 Or 1 / 0 Then
The statement above will fail on the division by zero error even though the first condition should have had been sufficient to satisfy the If/Then. We didn’t have to evaluate the second expression but the If/Then statement with Or operator is “eager” and wants to evaluate all expressions before deciding if the result should go into Then branch or into Else branch. This is despite that a single True condition is all that’s needed to pass the Or operator’s test. When we re-express it as a Select Case:
Select Case True
Case 1 = 1, 1 / 0
The “1 / 0” expression is never reached because the first expression will satisfy and yield control to the inner body immediately, hence the term lazy evaluation. In the MyConnection procedure, we are testing:
Case c Is Nothing, c.State = adStateClosed
Now, what do you think would happen if we asked about an object’s State property while the object is Nothing? If an error, you’re exactly right. We don’t want to check the State property if the object is Nothing, hence the Nothing test is the first condition. So when we use Select Case, we evaluate the object’s State property only when we have already tested that the object is not Nothing, making our code error-free. More importantly, this give us much better flow than nesting If/Thens where we would have 3 branches, 2 of them doing essentially the same thing.
But what about DAO.Database?
Not all objects, such as DAO.Database, are directly creatable nor they necessarily expose a property reporting their state. In this scenario, we actually need two procedures. Here’s how we’d handle DAO.Database:
Public Property Get TempDb() As DAO.Database
On Error GoTo ErrHandler
Static db As DAO.Database
If Len(db.Name) Then
Set TempDb = db
Select Case Err.Number
Case 91, 3420
Set db = CreateTempDb
If db Is Nothing Then
Err.Raise vbObjectError, "TempDb", "Creation Failed."
Private Function CreateTempDb() As DAO.Database
On Error GoTo ErrHandler
Set CreateTempDb = DBEngine.CreateDatabase(...)
Set CreateTempDb = Nothing
We use a simple test such as Len(db.Name) to simultaneously test for possibility of db being Nothing and/or db being closed. This is because we have no State property for the DAO.Database object so there’s no simple way of knowing it’s closed as there is with ADODB.Connection object. Since asking for the Name property on a Nothing or closed Database object would cause errors, we use the error handler to call a private helper function that actually performs the creation. One primary reason for farming out to a private function is because creating a new database could cause new errors, and we’re already in the error handler. We cannot nest multiple error handlers (it may appear to be legal but it just won’t work that way). Thus, we farm out into a separate function which has its own error handling and thus set the return to Nothing in event of failure. If you put those two procedures into a module of its own, the helper function would be accessible to exactly only one procedure, adding a safety check to avoid additional creation of the TempDb object.
Self-healing objects is great for Automation, too!
We would apply the same technique with other automation objects such as Excel.Application – who hasn’t gotten that pesky 429 error before? It’s galling because the 429 error is raised for both GetObject and CreateObject but means completely different things; “we couldn’t find a running instance” and “we couldn’t find any installation”, respectively. By using a public procedure with a private helper function we can always count on the automation object to be available at our beck’s call using GetObject first (grabbing any pre-existing instance) and falling back to CreateObject to create a new one. One common problem that appears on Access forums is someone wants to automate Excel but later discovers that an Excel instance is left dangling behind. While there are other reasons (Bob Larson gives one such reason), the self-healing object pattern helps guarantee that we only have one instance of Excel at all times and never worry about cleaning up in every routine where we use Excel automation (or any other automation as well).
All in all, I think you’ll find the self-healing object pattern extremely useful and so darned convenient. Happy Coding!
Mr Ben Clothier
Your article is a must.
Even to me whose mother language is portuguese it will serve a lot.
Nathan G Fiterman
I just noticed that you have defined the connection as a property. Is there any reason for doing so, as opposed to using a function? Normally I use properties for class objects, but not within “free” modules, where I would define it as a function.
Glad you found it useful, Anders.
For your question — as far as I can tell, there is actually no functional difference between a property procedure and a function in a standard module (besides the obvious point that you can have additional Let/Set procedures of same name, of course, but this is not applicable here since we don’t have any module-level variable). In my case, I guess it’s more of documentation for my own sake — that is, a property indicates to me that I’m re-using an object, as opposed to creating a new object.
Thank you for clarifying. That makes sense. I just wanted to ensure that there is not some benefit I am missing out on by declaring it as a function instead. I have always considered properties to be tied (exclusively) to class objects. I believe that in VBA modules are some sort of subclass though I have never investigated that further.
Thank you for sharing this well-written article.