Ok, so in some cases, VBA is nice to you and destroys variable references you use when you utilize the SET keyword.

Let’s say you do something like this:

Dim dbObj As DAO.Database

Set dbObj = CurrentDb

This is in the context of some kind of function or sub, or maybe inside an event handler for a form. In any case, what might happen when VBA finishes executing the function is that VBA will notice that you created an object variable and then if you haven’t done anything else with it, it will run the following code if you have not:

Set dbObj = Nothing

Now, that is wonderful, as long as the garbage collector actually works, but many times it does not. And it definitely does not if you have defined the variable outside of the local scope in a global scope whether in a Class, Module, or Form / Report module code.

You want to make sure that you EXPLICITLY set all your objects to Nothing when you are done with them. Otherwise you can get strange behavior in Access, like Access not actually shutting down, or repopening a process. Sometimes Access could just shutdown with no warning.

So, in each place you use the SET keyword in VBA, make sure you have a matching SET xxx = Nothing statement to match.

If you are doing this in a Class, one way to do this is to use the Class_Terminate event and make sure any variables that might get SET inside the class are set to nothing in this event. It will always run whenever the class is destroyed.

In a similar vein you can do this is a Form or Report CLOSE event. This always fires as the form or report object is closing so it’s a good time to SET any object variables you’ve defined to Nothing here.

If you have code in a public function in a module you use, if possible, SET the object variable to nothing when exiting the function (make sure you do this before an “Exit Function” or “Exit Sub” statement as well!)

If you are setting a public variable with a global scope in a normal module and you need it to persist between multiple function calls, I would highly recommend refactoring this logic to live inside a class module. That way you can control the object variable by putting it in the class module and triggering the Set Nothing call in the Class_Terminate method.

This will potentially save you a LOT of headaches!