Closing Database Objects: Explicitly Required?

[Galaxiom]

Let me ask you this, Greg. Did your Mum ever tell you to clean your room and put your toys away even though you KNEW that tomorrow you would take them out again?

Yes. She also told me to eat everything on my plate because wasting food was immoral. However I don't see what anything about my mother's thinking has to do with this issue.

Such analogies are rarely as valuable as those who promulgate them would have us believe. In this case it could be considered as a case of "clean up your room or the boogie man will get you" because you have failed to demonstrate any evidence to back your paranoia.

My viewpoint comes after 28 1/2 years of cleaning up other people's messes on a mainframe-class system.

All very well but we are taking about Access on Windows, a far more modern environment, in this thread.

Do you clean up your variables by letting all your strings to ZLS too?

Fact is, it can be unambiguously demonstrated that, provided the Quit Method is run, the automated application will not persist after going out of scope regardless of whether the pointer is set to Nothing or not. What else do you fear is going to persist when the application is gone from the Task Manager?

I am beginning to suspect that you are one of the Primates in the religion of SetToNothing.

 

[NauticalGent]

What else do you fear is going to persist when the application is gone from the Task Manager?

I was under the impression the the idea was to close RS's and DB's as well as variables when a Sub or Function ended and not necessarily when Access closed.

That is not to say the topic has not come up; my attention span and memory is not the best...

I guess the way to test this would be to check if any of them still were open or held a value once the Sub/Function went out of scope...or am I over simplifying this?

 

[Galaxiom]

I guess the way to test this would be to check if any of them still were open or held a value once the Sub/Function went out of scope...or am I over simplifying this?

That is exactly what it is about.

The religious observers insist the code must clear the pointer's reference before it goes out of scope or something they are unable to specify will persist in the memory.

 

[The_Doc_Man]

Greg, I wasn't born yesterday.

I know that if a variable is a local non-object variable in a subroutine, it needs no attention because it is part of the call frame that is automatically removed on Exit Sub/Exit Function. No problems there. Windows apps learned that trick from VAX & OpenVMS during the time that Microsoft and Digital Equipment Corporation had a teaming agreement.

If something is a local structure created by a New() function or variant thereof, it is in the program heap. Things in the heap, if carelessly left unattended and created anew without explicitly discarding or re-using them, will over a long session have the potential to fill the heap. TempVars go there, for example. And remember that if you are using a switchboard form or dispatcher form, it stays open for the life of the session. Anything it created along these lines will stick around, too.

Using 32-bit Office, you have 4 Gb of memory space because that is the memory model that was in use when Office integration started. However, specifically for Access, at least as I understand it, the database has 2 Gb of that space and the program has 2 Gb, and the program's 2 Gb has to accommodate the base .EXE code, all .DLL files, the program stack including all stack frames, and the heap. The .EXE code isn't so bad in terms of size but for a complex app, you chew up a lot of address space if you have a lot of .DLLs. If you have complex subroutines with local array variables and they do a lot of calls so that you have many frames on the stack, that gets dicey after a while. And let's NOT discuss recursive routines, which I HAVE implemented in VBA. If you then carelessly leave crap around in the heap, you face the chance that the stack will touch the heap. When that happens, it is "goodbye, application, time to shut you down."

Microsoft HAS gotten better at address layout vs. Ac97 where I first started using Access, but careless memory habits can still eat address space. And Greg, I'm TELLING you the mechanism for what happens on a failure to clean up allocated structures. What more CAN be said?

If we are talking about any kind of passive object variable, then you cannot ignore it completely because the object variable is a pointer to the "real" structure somewhere in the session's heap storage. See HEAP discussion above.

Remember also that we are running pseudo-code operations because VBA is not compiled to machine code like VB6 would be. The implementation of the SET xxx operation has some "smarts" behind it so that a SET xxx = Nothing can not only reset the pointer but also release the created object behind it.

Now, there can perhaps be an argument regarding whether an Exit Sub/Exit Function will detect such objects as part of the stack and will clean them up properly. Since that, too, is emulated, the emulation certainly COULD do that. I have found cases in this forum where one of these "internal" objects made an external connection that needed attention and the simple solution was to just close the object before you set it to nothing or exited, whichever came first.

If it is an application object, there is also an independent entity floating around in a separate process with its own address space. That entity needs to be told to shut itself down. You said it yourself... just tell it to Quit and it will go away. And I absolutely agree with you. After doing that, the problem devolves back to the question of whether the object-handling structure inside of Access gets cleaned by Exit Sub/Exit Function, and again, if it is based on something allocated in a subroutine, then it surely could be cleaned in that manner. But if you are not sure, there is the old adage about an ounce of prevention.

My point is that PEOPLE DON'T BOTHER with even simple programming prophylaxis unless you give them some simple reminder. For a seasoned programmer, it is not an issue. But Greg, do you HONESTLY BELIEVE that everyone we see on the forums IS a seasoned programmer? My advice is for them until they become more aware of the complexity behind the scenes.

When you were in school, did your teacher give you the ROY G BIV mnemonic for the colors of the rainbow? When you were in school, did your teacher tell you the rule about "I before E except after C"? When you were in school did you learn the rule about "drop the Y and add IES" (for pluralization of words ending in Y)? They were mnemonics designed to help you remember things until you learned them based on more advanced methods of remembering. Don't you see that I am offering the same thing?

I am beginning to suspect that you are one of the Primates in the religion of SetToNothing.

Greg, I am easy-going about this sort of stuff and sometimes I get misunderstood, so I won't let myself be insulted by that. Hell YES I am a primate... Animalia Chordata Craniata Mammalia Primata Hominidae Homo Sapiens (and these days, for some reason, the taxonomy wonks add an extra Sapiens). But I don't worship Set To Nothing. I just offer reminders to folks on how to avoid certain pitfalls until they learn which variables, objects, and structures CAN be safely ignored - which they learn from discussions such as you and I are having right now.

Let me pose a question for you, Greg. If Set xxx = Nothing is so unnecessary, why is there explicit syntax for it and why do MSDN sites describe it with their examples on use of structure-related objects?

Addendum: After re-reading this, I recalled an old Gary Larson cartoon from Playboy where a bunch of guys were bowing prostrate before a pedestal with a box with the big letter "N" on it. One of the worshipers whispered to the other, "Is Nothing sacred?"

For the record, I wasn't there bowing before Nothing.

 

[NauticalGent]

My head hurts...

 

[The_Doc_Man]

Gizmo, I actually have an answer for you. [Showing my age] Back in the early days of BASIC, you needed the LET because syntactically it was an introduction to an expression and ALSO was a mnemonic to show that LET xxx = expression was actually an executable statement, not an assertion of an already-existing equality. However, once other 2nd generation and 3rd generation languages showed that you didn't need it, more modern versions of BASIC learned to do without it. Therefore, the LET verb, though still I suppose technically valid, is now in disfavor.[/Showing my age].

I think the "property LET" that you mentioned is simply a special case for which a handy little verb was around to make things easier to define. As Object Oriented Programming becomes more and more popular, LET and GET become functional in a special way.

 

[Galaxiom]

When you were in school, did your teacher tell you the rule about "I before E except after C"?

Yes. However this rule has been officially dropped in Australian schools because there are so many exceptions that it was deemed useless.

I am easy-going about this sort of stuff and sometimes I get misunderstood, so I won't let myself be insulted by that. Hell YES I am a primate... Animalia Chordata Craniata Mammalia Primata Hominidae Homo Sapiens (and these days, for some reason, the taxonomy wonks add an extra Sapiens).

I was not inferring you are a Simian. I was using Primate in the sense of the Archbishop of Canterbury is a Primate of the Anglican Church.

 

[The_Doc_Man]

Given my known religious stance, which includes a rigid digit to any organized religion, I doubt I would fit into that particular mold.

Usually, folks who get frustrated with me will suggest that I'm half a horse - the half that doesn't whinny!

I'm not trying to be a hard case about this, you know. It should be obvious that I fully understand the mechanisms involved. It is almost NEVER the mechanisms that get you. It's always people who toss the monkey wrench into the works.

 

[The_Doc_Man]

Uncle G:

Lately I get pulled up for using a Function where a Sub would do.

You know, the one that always gets ME is why the hell when you do a Macro RunCode action, you have to name a function, not a subroutine. Macros don't have very good error handling anyway, and I don't think there is very much they can do with that return value, so why bother? But it is required to be a function.