The problem happens when object A has a reference to object B and object B has a reference to object A. It can involve more objects, too:
A => B => C => A
This is called a "circular object reference".
VBA does automatic garbage collection. That means that when the last reference to an object is destroyed, VBA can tell that no more references exist and it frees the object. Automatic garbage collection saves the programmer from having to explicitly free objects, which is nice. For instance, if you were using DAO Database, TableDef, and Field objects like this:
Code:
Set db = CurrentDb()
Set tdf = db.TableDefs("My Table")
Set fld = tdf.Fields("My Field")
then without automatic garbage collection you would have to loop through the Fields collection, freeing each field; then loop through the TableDefs collection, freeing each table definition, before you could Set db = Nothing.
Going back to the circular reference involving A, B, and C above, suppose that A is the top of the hierarchy of objects. You would have an object variable that refers to A, from which you can get to B and C. Lets say your object variable is named X.
What happens when you set X to Nothing? Usually, automatic garbage collection would notice that object A has no more references and would therefore free it. But when you have a circular reference, as above,
there is still a reference to A (it's in object C), so VBA will NOT free A. X will have been set to Nothing, so your code no longer has any way to refer to A, or B, or C. Nothing you can do will cause VBA to free these objects, and the fact that there are still objects running will make Access stall when shutting down.
If you avoid circular object references, you won't have a problem. If you
can't avoid them, you have to take extra care to break the chain of circular references somehow before setting object variables to Nothing. In this case, the easiest way would be to set A's reference to B to Nothing, before setting X to nothing. Suppose A's reference to B is in a property 'BRef'. Your code would look like this:
Code:
Set X.BRef = Nothing
Set X = Nothing
This isn't always enough. Consider the following more complex situation, involving two circular references:
A => B => C => A
C => B (that is, C references both A and B)
If you merely ran the code above, the circular reference between B and C would still keep them "running" so they wouldn't be freed. A would be freed, however. The upshot is that you must break EVERY circular reference in the structure of objects you want to destroy.
Ok, so how hard is it to avoid circular references? In a simple object model, usually not hard at all. But in an object model that contains Collection properties, it gets tricky. The problem is that you will often want the objects stored in the collection to add or remove themselves from the collection. To do that, they would have to have a reference to the Collection object, or to some parent object of it. And that creates a circular reference. I haven't found any good way to avoid that problem.
Access and DAO object models avoid it somehow (except for the DAO bug I mentioned earlier), but I haven't figured out how they do it, so I have to do the careful cleanup as in the final code sample above. In Access and DAO, the circular references hinge around Parent properties. I have noticed that Parent properties are always of type Object, even when the specific type of object referred to is known. I have wondered whether Parent properties are implemented as procedures that somehow
find the parent object, rather than storing a reference to it. But I haven't been able to figure it out.
If you have an MSDN subscription, you'll find a more thorough discussion of this problem there. Try searching for the phrase "custom object model" or "programming object models".
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein