Is Manual Memory Management Necessary in Modern Day Access? When Does Garbage Collection Occur?

[SparklySpartan]

Hi all,

I was wondering if I could get some of your opinions on whether explicit memory management in VBA code is a good practice / necessary in modern day VBA.

Here's my current understanding, please correct me where I'm wrong. When an object, such as a recordset, goes out of scope, that is to say, the context in which it's being used ends, assuming a reference to that object wasn't returned somewhere else to be used further, VBA's garbage collection will automatically de-allocate the memory that was being used by that object and give it back to the operating system, preventing a memory leak from occurring.

I don't know when exactly this happens, whether it's right when the reference to the object goes out of scope or later, so maybe, explicitly calling Recordset.Close and setting the reference to nothing could be advantageous. But if it turns out the memory always gets de-allocated automatically, right away, anyways, I would consider these two lines of code redundant.

The question of when garbage collection occurs is my main curiosity, and it feels like the deciding factor of whether or not this is a good practice. I've been writing code that has not been explicitly doing these 1-2 steps for every object and haven't noticed any issues, but if it turns out it does matter, I'll go ahead and start doing so.

Thanks in advance for your responses.

 

[MajP]

explicit memory management in VBA code is a good practice / necessary in modern day

That is not really a question AFAIK since it is not possible. There is not explicit memory management in VBA.
Pointer references are added and removed. At some point garbage collection takes place and you do not know explicitly when that happens nor can you influence it.

 

[SparklySpartan]

There is not explicit memory management in VBA.

Oh I see. I thought the recordset.close method caused the memory to be de-allocated, but really, all it does is remove the recordset's internal pointer to that memory, in the same way setting a recordset variable to nothing removes the pointer to the memory used by that recordset object. Is that more correct?

At some point garbage collection takes place and you do not know explicitly when that happens nor can you influence it.

In that case, it seems that setting the pointers to nothing is unnecessary if you're doing it at the end of a sub or function, since when the pointers go out of scope the same thing is going to happen automatically.

Thanks for clearing that up!

 

[Isaac]

The only, one, single thing I've heard that (may or may not be of interest to you) .. is:

When looping, with an Open and Close DAO recordset inside the loop, it may help to set the recordset variable to nothing after closing it.

I can't remember who, but someone I really trusted once told me this. (?) Open to being wrong, just mentioning in case it helps.
And not sure it has directly to do with Memory.

 

[The_Doc_Man]

Here's the best I can tell you on Access garbage collection.

When you enter any event code routine (which counts as a sub) or explicitly call a function/sub routine, any variables defined in that routine's declaration area are dynamically allocated on the Call Stack in the routine's context area, which is part of the structure known as the Stack Frame. These dynamically allocated variables exist until you exit the routine that declared them, at which time they are deallocated by removing the Stack Frame and everything in it. These former variables are now in the area just "below" (lower addresses) the current top of the stack - which really should be called the BOTTOM of the stack, but common usage calls it otherwise. This memory area just below the active stack is immediately reusable on next entry to anything that requires a call frame. The "new" call frame overwrites the place that held the previous call frame. The only variation here is the size of the local variable context area that is somewhere within that call frame. You ALWAYS have a context area but it doesn't have to be very big. Because of the use of stack-based storage and the Call Frame concept, all local variables are immediately self-cleaning.

When you have a general module, it can have a declaration area between the start of the module and the first entry point declaration for any sub or function. Anything in a general module's declaration can be PUBLIC and will always be available once the general module is activated by using or referencing something in it for the first time in the session. Variables in this category never get cleaned up until the task exits. PUBLIC or PRIVATE, all variables declared in general modules are treated in the same way described for STATIC variables.

There are two exceptions to memory reclamation rules.

First, STATIC variables are allocated similarly to PUBLIC or PRIVATE variables defined in the declaration area of a general module. They stick around for the life of the session. They get cleaned up only when the task exits, at which point Windows moves the memory pages to the Free memory area if they were not shared or, if the memory was being used in a shared style - e.g. the "pure" code portion of a shareable task image - then it MIGHT go to the Standby memory cache, or (if STILL actively shared by other tasks) it might stay in the In Use or Modified memory cache. You can open Windows Task Manager and look at the Memory performance page to see the four memory types and how much of each type you are using. Hint: Performance-wise, Free and Standby taken together should be the largest memory chunk in the system.

Second, ANY variable that implies a structure - internal or external - has the CHANCE (not a certainty because it depends on what it is) to act as a pointer to a real structure contained elsewhere within the program Heap. If it is a type of object for which the NEW keyword makes sense, it is definitely a point to consider. Recordset object variables always point to some infrastructure behind the scenes. Application objects might point to actual external tasks like you get if you open an Excel or Word app object. The catch here is that the actual object might not automatically get reclaimed immediately - or at all.

The worst cases (from a memory management viewpoint) are external app objects, which are embodied as tasks that have a life of their own. If you create a Word, Excel, Outlook, or any other utility program object, you must remember to tell it to quit in whatever style is right for that program. For Office members, the syntax is app-object.Quit, but it is not so clear for non-office programs that can become app objects.

A lesser but non-zero problem is recordsets that are created in the program Heap. Dynamically created arrays also can be a problem, as can TempVars and Dictionary objects. The Heap cannot be garbage collected by anything except exiting Access itself. The inability to reclaim memory is not, in itself, a problem UNLESS you start getting messages like "HEAP overflows STACK" (or the other way, "STACK overflows HEAP"). Either of those means you have a problem of using too many dynamic structures or calling something recursively... anything that consumes the stack and/or the heap.

Windows WILL re-use some of its internal structures, such as file handles, but you have to close the associate file to allow such re-use. Therefore, for ANY object that opens a file or recordset, when you are done with it, remember to close it. Closing will, among other things, force any pending ("dirty") file buffers to be written before the file actually closes. In the case where the object variable is created in a private SUB , it SHOULD be enough to exit the sub and let subroutine EXIT operations clean things up for you. However, some time ago we had a Microsoft person on board who said that sometimes things go wrong, and it was ALWAYS a good idea to CLOSE the object even if you couldn't guarantee that it would be deallocated later.

 

[MarkK]

FWIW, I explicitly close recordsets, but I do not routinely set recordset object variables to nothing. The way I understand it is that VBA garbage collection operates on reference counts. This means there is a VBA internal structure mapping declared object variables to existing object instances, and when an object instance has zero valid reference to it, then it is available for garbage collection.
That being the case, just letting the variable go out of scope at the end of a procedure, IMO, is sufficient.

To discuss Recordset.Close I'll first show how to create a memory leak. Create an instance of this class...

Private me_ As Object

Private Sub Class_Initialize()
    Set me_ = Me
End Sub

Because this class contains a reference to itself, therefore if this instance exists, there will be a reference to it, and since there is a reference to it, it will never be available for garbage collection. This is what a memory leak is. To solve this we can add a close method.

Private me_ As Object

Private Sub Class_Initialize()
    Set me_ = Me
End Sub

Public Sub Dispose()
    Set me_ = Nothing
End Sub

Now we have an explicit way to cause the object to release any internal resources that MAY leak memory. Call Dispose in this case, and the VBA reference counter goes to zero, and the object will be garbage collected. This is, as an aside, how you can keep the non-default instance of an Access.Form open.

So based on this logic, if some other programmer bothers to expose a .Close, .Dispose, or .Invalidate method, then I will accept his word that it matters, and routinely call that method during clean-up, but I won't necessarily set an object to nothing for that same purpose.

There is one exception in the rare case that a object holds a reference to a WithEvents variable that it is not the sole owner of. Internally, in order for a WithEvents variable to work, VBA holds a reference to the object that might raise an event and all of its subscribers. It must do so in order to know who to notify if the event is raised, and these references are duly counted for the purposes of this discussion.

So if ClassA and ClassB both declare a WithEvents variable, and you assign to both variables the same instance of an object (maybe some kind of global event notifier), then when ClassA goes out of scope, it cannot automatically destroy its WithEvents variable (because ClassB still has a pointer to it). And since that variable cannot go out of scope, its subscription list cannot go out of scope, and ClassA remains a member of that subscription list. Furthermore, VBA has no reason to remove ClassA from the subscription list, because it is still valid. This a memory leak implemented as a circular reference chain, and ClassA will continue to handle events raised by its WithEvents variable, even though there are no valid references to it in your code.

In this case you MUST explicitly set ClassA's WithEvents variable to nothing, or ClassA cannot go out of scope.

That is a pretty complete discussion of what I have encountered dealing with object life cycles in VBA.

 

[MajP]

Oh I see. I thought the recordset.close method caused the memory to be de-allocated, but really, all it does is remove the recordset's internal pointer to that memory, in the same way setting a recordset variable to nothing removes the pointer to the memory used by that recordset object. Is that more correct?


In that case, it seems that setting the pointers to nothing is unnecessary if you're doing it at the end of a sub or function, since when the pointers go out of scope the same thing is going to happen automatically.

Thanks for clearing that up!

Kind of two different things.
There is no Manual Memory Management in VBA, it uses Automatic Memory Management or Garbage Collection.
Here is an explanation

Strategies for Managing Heap Memory​

The management of heap memory is a critical area of focus in programming due to its flexibility and the complexities involved in its management. Here are four common strategies found in programming languages:

1. Garbage Collection: Used in languages like Java and C#, garbage collection automates the process of memory deallocation. The garbage collector periodically scans the heap to find and free memory that is no longer in use. While this reduces the burden on developers, it can lead to unpredictable performance dips.

1. Reference Counting: Implemented in languages like Python, this technique involves tracking the number of references to each allocated object. When the reference count drops to zero, the memory is automatically reclaimed. The downside is that it can’t handle cyclic references where two objects reference each other.

1. Manual Memory Management: Seen in languages such as C and C++, manual management requires developers to explicitly allocate and free memory. This approach offers maximum control but increases the risk of bugs like memory leaks and dangling pointers.

1. Ownership System: Rust introduces a unique model of memory management through its ownership system, which enforces rules at compile time to ensure memory safety without the need for a garbage collector. This system includes features like borrowing and lifetimes that help manage memory efficiently and safely.

VBA falls into #1.

So you can remove a pointer early by setting some variable to nothing, but that does not free up memory. As stated, eventually the data collector gets around to scanning the heap and will free up the memory.

People go crazy about setting objects to nothing, and for most part that is a complete waste of time. When a procedure goes out of scope that object becomes nothing and a pointer decrements. People will argue, but sometimes it does not so better off being safe. I say prove it. But since few people seem to know the rare cases when you should set an object to nothing you are safe doing it often. Read this.
https://learn.microsoft.com/en-us/a...en-are-you-required-to-set-objects-to-nothing

Explicitly closing databases and recordsets is often a good thing. This is a completely unrelated topic.

Places where I will set an object to nothing.
1. Automation objects such as a reference to an excel sheet.
2. Classes properties that reference physical things like forms, controls as class properties.
3. From a method that may call other methods and it might be a while until control comes back to the calling method
4. Composite class properties as mentioned in the article.