Garbage Collection

[JimS]

I'm not careful to close and/or set objects to "nothing" when using ADO or
the Excel object model from inside Access forms using vba. What symptoms can
result from this lazy behavior? I have had some very strange things happen,
and I can't understand them. I seem to be able to fix them only by a cold
reboot....thus my suspicion about memory leaks.

FYI, most forms are short-lived, as are the objects (recordsets,
connections, etc.) The form using them is closed within minutes. I would
assume that an object created by a form class would deallocate all its
resources when the class is closed... Am I wrong about this?

 

[Klatuu]

You reall need to be more structured in your coding. It is always best to
close objects and set the object reference variable to Nothing.

You mentioned Excel. If you are using automation to manipulate an Excel
object. You need to be even more careful. If you don't completely reference
all objects, Access may not be able to tell which instance of Excel you are
referring to. It will create its own instance. So even if you carefully
Close your work book, Quit the Excel Application object, and set the
reference variables to Nothing, you may leave behind an instance of Excel in
memory. You will find it listed in the Task Manager Processes tab.

 

[JimS]

I just started a new Excel routine last night, and this morning I've had to
reboot twice. Ur probly right. The biggest reason, besides being lazy, I
don't close and set to nothing has been that I do it at the wrong point, and
get yelled at by the debugger. I delete the snippet, get the routine working,
then forget to go back and clear my objects.

I'll watch for the Excel processes.

The issue I had Monday (before starting Excel work), was Access literally
misbehaving. I would start up a brand new query, put two tables into the
Query builder, and try to remove the errant join (joins two "IDs"). It
refused to allow it. Delete was greyed out.

So, I tried changing the option so it shouldn't create that auto join. It
created it anyway. Again, I couldn't delete it! Rebooting solved it. (I use
Citrix and remote desktop to connect to my client's computer, so rebooting
can be a pain...)

Today, I got "out of memory" messages when I tried to cut a string from a
routine in the IDE. Wasn't just Access, though, I couldn't use the clipboard
at all.

I'll go back in and clean up the code. Thanks!