Self-healing Objects

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")
End If
Set FSO = f
End Property
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

Set MyConnection = c
End Property
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
...
End Select
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

Private Function CreateTempDb() As DAO.Database
On Error GoTo ErrHandler
Set CreateTempDb = DBEngine.CreateDatabase(...)
ExitProc:
Exit Function
ErrHandler:
Set CreateTempDb = Nothing
Resume ExitProc
Resume
End Function
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!