Closing Database Objects: Explicitly Required?

[The_Doc_Man]

And again, Greg, we come to the issue that people are fallible. They need to be REMINDED that there are things they should do regarding cleanup. If nothing else, the "If you opened it, close it. If you started it, stop it. If you let it run around independently, tell it to quit" advice is a simple mnemonic for people dealing with potentially complex code. Code that gets so complex that they begin to forget whether they wound the cat and put out the clock at the end of the day.

In the other thread that I referenced, I made it clear that automatic cleanup WOULD occur for some things. My point was and is that cleanup is a good idea because then you KNOW it was handled. 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?

My viewpoint comes after 28 1/2 years of cleaning up other people's messes on a mainframe-class system. (It was one of my two major duties, the other being some database support work including but not limited to the Access database where I got really deep into the innards of Office.) Developers, despite knowing how to make things work, never seem to learn how to make things work without leaving behind a mess.

You say, "All you have to do is tell the app to Quit." YES, YES, YES. And without some sort of blind mantra to remind them, THEY STILL DON'T DO IT! Perhaps I've become somewhat of a "people pessimist" but my position is that without reminders, people are SLOPPY. They take short-cuts. And it was guys like me, a systems analyst / programmer / administrator who had to clean up other messes.

My unofficial title for more than a couple of years was "Senior Systems Janitor." For about the first third of those 28 1/2 years, nearly 70% of my job was cleaning up my system because users didn't clean up after themselves. When you run out of resources because people are too lazy to clean up, or they don't understand the concept of finite resources, it ends up as a bad situation. I can't tell you how many times I had to reboot a production time-share system in the middle of the day to unhang processes that would not go away on their own and that blocked operation of other users. It happened so often that I lost count.

I know, it sounds like I'm a terrible pessimist who thinks people are dumb. But that isn't really so. Let's look at Murphy's law: If it CAN go wrong, it WILL go wrong. Simple, right? No, it isn't. Because the part that everyone remembers is only HALF of the REAL Murphy's law. The other half is "So make it impossible for it to go wrong." That's why we now have polarized, asymmetrical plugs, the absence of which was the cause of his formulation of that rule in the first place.

That's why I tell people that by making it a habit to clean up after yourself, you MINIMIZE the chances of making rookie errors that leave behind a mess.

Sorry, I kind of got on my soap-box for a moment. I'll tone it down. But I WON'T erase what I wrote.

 

[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.

 

[Uncle Gizmo]

When I first started coding I always used "Let" I was informed several times that it was unnecessary and may break my code if I keep using it. So I stopped using it.

As I got more proficient, I discovered "Custom Properties" and they use the "Let" statement. Should I go back to using the let statement or not? That's a rhetorical question, to lead into my answer.

My answer is this Live and Let Live.. (unintentional) Let people do the job the way they feel it's best as long as what they are doing has no detriment to the code. As to the issue of whether you have to close things or not, well that's a different kettle of fish, and I am not going to say anything about it except that if the different states have been tested with no Ill effects then why worry about it?

Curiously, if it's not necessary to set to "Nothing", then why is it included in the language? Rhetorical again Same as "Let" it's not necessary, you can use it or not. It might be interesting to explore the history of VB you might discover that at one time it was necessary to use "Let" and it was necessary to close and set to "Nothing". Lately I get pulled up for using a Function where a Sub would do. I'm not convinced it's wrong, and I don't see any detriment to using one to the other. But that's a discussion for a new thread!

We all have had different experiences in getting to where we are with it, I think it's reasonable to assume that most people within the forum are competent and also willing to learn. We all have a shared interest in MS Access for whatever reason. If we can't be respectful and accept our differences in this very specialised interest then there doesn't appear to be much hope for the world at large...

 

[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.