Closing Database Objects: Explicitly Required? (1 Viewer)

I'm using MS Access 2003.

I have the following code behind a command button:


In the Access documentation for the Close command, I see the following two statements:

Caution If you exit a procedure that declares Connection, Database, or Recordset objects, those objects are closed, all pending transactions are rolled back, and any pending edits to your data are lost.
...
An alternative to the Close method is to set the value of an object variable to Nothing (Set dbsTemp = Nothing).

Click to expand...

Two (perhaps dumb) Questions:

1. Since I exit the subroutine in my CleanUpAndExit section, do I really need to explicitly close the database? Should I also set it to Nothing? I see lots of preaching online about the importance of Closing these objects or setting them to Nothing so as to free up resources, but these Help statements seem to say it's unnecessary.

2. If I needed to call multiple functions that run commands against the database and no transaction processing was required, is it better to declare the database and workspace in the called functions or to do that at the highest level and pass those objects into the functions, as I have done here? (Just wondering if there's much of a performance hit for doing that multiple times in the called code versus just once at the highest level.)

Thanks for any clarification you can give.

Wayne

No, you should not close the database with myDatabase.Close unless it is a reference to an external database. If it is the current database the close command will be ignored and yes, you should set to Nothing as best practice. Recordsets on the otherhand should be closed and then set to Nothing.


You will not notice a difference in performance difference if you change the scope of the currentDb variable. I tried a number of different approaches to this including exposing it as a kind of singleton using a property in a module to return a private backer that is set to CurrentDb on first use only per session. It made no difference to my timings.

I tend not to use Workspaces, but that is because almost all of my data is in linked SQL Server tables.

>>I see lots of preaching online about the importance of Closing these objects or setting them to Nothing so as to free up resources, but these Help statements seem to say it's unnecessary.<<

All it is is people quoting other people who have quoted other people ad nauseam.
Eventually so many people are saying the same thing that almost everyone agrees that they need to conform to the opinion of the masses.

In effect all they are doing is agreeing with themselves. In reality what you will find is that not one of those people will be able to prove it. They will posts links to some other person who agrees with them but that other person will not be able to prove it either. Not even Microsoft can prove the need to close.

The discussion could be argued in reverse. If so many people had first hand knowledge of the need to close then there would be more samples of the proof. But there aren’t any samples of the proof and so there is no first hand knowledge. The discussion is based purely on the mass of people quoting each other. Eventually the argument for closing will be “It’s better to be safe than sorry.”

Well, on the other hand, it can be proved that we need to be very careful when we do attempt to close a recordset. It is actually quite easy to write code which will make Access go to the task bar and not shut down. If we then go to Task Manager, Access will be stuck there and consuming about 50% of machine time. Very easy to write and quite subtle.

Just ask for one Jet demonstration of a failure caused by not closing and you will not get it.
All in all, the need to close is a fallacy that no one can prove.

Chris.

I agree that there is an element of the "5 monkeys in a cage" experiment" about this but I also know that it does no harm to close and set them to null which is why I said that it is "best practice".
I am sure that the number of people who do it is much higher than the number of people who think that they need to do it.
Maybe someone should set up a poll of experienced VBA developers sometime.

>>Maybe someone should set up a poll of experienced VBA developers sometime.<<

That poll has been running for many years, I’d guess around 18 years, and the collective opinion is that it needs to be done. But it is a self generated collective opinion because nobody, not even Microsoft, has supplied verifiable proof. The longer people have been chanting the mantra the more disinclined they are to change their tune.

In the past few months I have noticed two MVP’s soften their stance on the subject and I hope more will follow. But I only want them to agree if they do so on fact not on folklore.

Now bear in mind here that if Microsoft finds a bug they, to their credit, are not averse to publishing details of that bug. Often they will demonstrate how to reproduce the bug but with this subject there is not even any detail.

And it’s not just the “5 monkeys in a cage” but also "The Emperor's New Clothes".

(Funny how coincidence goes. You mentioned the “5 monkeys in a cage” which involves a banana and there is a member of this site called Banana. You also mentioned the word “singleton” in a previous post and there is a Singleton Class mentioned in the UtterAccess Access Wiki. One of the members over there, who has made some contributions to that Wiki, is named Banana Republic. What a coincidence; almost enough there to start our own rumour. )

But back to business…
>>it does no harm to close and set them to null (Nothing)<<
Well that depends on how it’s done.

There is an Access 2003 database attached.
It contains one Table and one Form.

This is the code behind the Form:-

We can open and close that Form until the cows come home and nothing much happens.

Now use the command button wizard to create a button on that Form which shuts down the database. Open the Form and click on the button.

How many times over the years would that have happened and did the recordset get incorrectly blamed for it?
It’s not something even a good Access developer will see simply by looking at the code.

Chris.

For a more realistic test this would have five people sharing an access database open a linked table to a sql server database with 10 million rows and move a few thousand rows in and then close the form without closing the recordset. I have seen this leave active sessions and locks open in SQL server. Close the form and repeat 10 times. Can you guarantee when the GC will close the out of scope recordset and release the locks? This may and probably will work 99% or even 100% of the time but I feel better knowing that I have closed the recordset. Setting it to Nothing is less likely to affect anything but I do it anyway.
A one user test on a local table is not enough proof for me.

That is part of the defence strategy of those who can’t prove it is necessary.

Confuse the issue to the point no one can test it and then state “It might work a billion times but will it work a billion and one times?” It has been done before but they can’t prove it.

On the other hand, the major symptom of the failure can be proved each and every time quite simply in other ways. So the question needs to be asked; why would an unprovable solution be adopted in place of a highly reproducible solution?

Is the reason for the highly reproducible solution understood?

I have posted a testable solution.

Chris.

Times up…

We should not attempt to close DAO Recordsets which are about to go out of scope.

Over the years the vast majority of people have claimed we need to do so. The primary reason given is that, if we don’t explicitly close a recordset, Access may not shutdown correctly.

Let us be very specific about the claimed symptom. It is claimed that Access will start to close, and may remain in the Task Bar, but it is still showing as being active in Task Manager. That which is shown on the screen is not important here, the important thing is that Access is still shown as active in Task Manager.

Some comments as they appear to me:
1. The debate has been going on for many years, probably more than 18.
a. Since the debate is still going on it has not been satisfactorily resolved.
b. If the debate had been satisfactorily resolved then there would be proof, not more debate.
c. The length of the debate indicates that no proof has been entered into the debate.
d. Therefore there is no proof.

2. Without proof the debate becomes only qualified opinion or hearsay.
a. Without proof, qualified opinion can still be valuable but it is still not proof.
b. Without proof, hearsay is not even valuable.
c. Therefore, there is only qualified opinion.

3. Who is qualified to test the qualified opinion?
a. The people who repeat things they read are not, it is hearsay.
b. The person with the qualified opinion is not, it needs testing externally from that person.
c. I am not, I can not even offer a qualified opinion and so it would be hearsay.

4. What can I do?
a. I can question.
b. I can ask why there is no proof.
c. I can read most, if not all, on the subject.
d. I can look at not only what has been said but also at what has not been said.
e. I can propose an alternative which appears to be missing from the debate.

5. The alternative.
Over the years a complicated reason has been given for Access not shutting down correctly. That reason has to do with the incorrect sequence of the release of Recordsets. The reason is sufficiently complicated to elicit a ’wow’ factor from the hearsay community. The hearsay community propagates the ‘wow’ factor to other members of the hearsay community. “Wow’ is better than dull so it propagates quickly and with gaining force.

The “5 monkeys in a cage” are reinforced and the "The Emperor's New Clothes" have arrived. The problem is that there is no proof of either.

6. The proposal.
There is no ‘wow’ factor and, sad to say, it is just plain dull.
The qualified opinion was wrong and it was just hearsay by the others.

The qualified opinion has no proof else it would have been posted in a verifiable manner, not just words, years ago.

The code as posted in post #5 is okay, as is.
It is when we add the code generated by the wizard that Access fails to shutdown correctly.

The code, as generated by the wizard, includes the line:-
DoCmd.Quit

That line of code starts a shutdown sequence.
One of the first things Quit does is to reset variables which are outside of procedures, both Global and internal to Form modules.
So:-
Private rst As DAO.Recordset
gets reset and, in the process, closes rst. (The garbage collector works, it just did its job.)
Further on in the shutdown sequence Forms which are open are required to close.
In closing a Form its Form_Close event is called.

In this case, when the code run hits the ExitProcedure Label, we do a:-
rst.Close
but the Recordset has already been closed by the shutdown sequence as invoked by the Quit command.

The attempt to do a Rst.Close raises an error.
That error takes us to the error handler.
That error handler resumes back to the cause of the error, ad infinitum.

Access can’t shutdown because it is too busy trying to handle the error loop.
It is not that the Recordset has not been closed, quite the contrary. It is because the Recordset has already been closed by the garbage collector. The garbage collector has done its job but the programmer may not know it.

Now, of course, all become knowledgeable overnight. The newly informed may say they do this or they may say they do that but that simplicity of an alternative cause of the symptom has been missing from the debate over the years. Read the literature; look for what is there and for what is not there.

Why is that simple alternative not in the debate? It is certainly not the only way to reproduce the symptom.

Why complicate things with unreproducible words when an entirely reproducible verifiable solution is available?

One, verifiable, alternative solution is available in post #5.
Bring on more verifiable solutions…


To try and answer the original posters question…
Do not attempt to close anything which is about to go out of scope.
To do so is an assumption that we know better than the people who wrote the garbage collector.

Just let it happen as intended…


Chris.

Times up? Grow up!

This is a point that polarises the VBA development community and most programmers assume that they know better than their peers.

There is no point arguing with someone who is blinkered in their opinion - I was trying not to be and to try to understand your point of view but maybe I failed in my last post.

You think that I, and others, write unnecessary code to clean up objects before exiting (recordset, COM objects etc.). But I would argue that I know best when I have fininshed with them they are okay to close and destroy. This takes a couple of lines of code and does not affect code performance. Yes, the garbage collector may well do this for me but I have no control over it so I do it myself. No user of my applications will ever be inconvenienced by me doing it rather than waiting for the GC.

You want the garbage collector to clean up after you, as do many other developers. I and others see this as lazy programming. A bit like "why should I clean my room when I know my mum will clean it for me!". If this works for you then please feel free to continue - just don't try to convince me that I should follow your example.

I would encourage everyone to make their own decision on how to close recordsets and any other objects and not be swayed by who shouts loudest (or last!).

And yes, I do realise that this is not the end of the argument, just the start of the next one.

While I do realise it is not VBA, one might consider comparing the advice given for .NET application design.

The Common Language Runtime Garbage Collector can tell when an object is not referred to again and, if memory space is required, will clean it up when it is of no further use. This happens before the procedure ends and before the object would otherwise be considered Out of Scope.

Adding the command to set the object to Nothing at the exit of the procedure actually stops the GC from removing it from memory when it is no longer used because of this final reference.

So in that case, not closing is far from lazy and actually avoids the object from persisting in memory unnecessarily.

Fact is we don't really know how the Garbage Collection works in VBA so virtually everything of what we see on the subject is speculation.

ChrisO has at least shown evidence for his opinion.

DrallocD.
>>Times up? Grow up!<<
No. Times up because I gave people, not just you, time to test the attachment I posted.
The original poster has a question and I think it should be answered as best we can.
May I also suggest you be careful?
----------

Galaxiom.
Thank you for your post. It is interesting that .NET programmers are advised not to.

While still not VBA, this link specifically addresses VB and VBScript, it does address the reason as to why people try to force a close.
http://blogs.msdn.com/b/ericlippert/archive/2004/04/28/122259.aspx

I guess what we a left with is that .NET, VB and VBScript seem to imply the same thing; don’t force a close just prior to going out of scope.
So a question needs to be asked; did Microsoft deliberately screw up VBA? I think not.
----------

And so back to general comments:

Here’s an often quoted link:-
http://access.mvps.org/access/bugs/bugs0005.htm
That link seems to imply we should close but I think that link has three problems.

The first is the use of the word “Whenever” in both the question and the first answer. The word “whenever” implies that it is entirely reproducible and yet no one can reproduce it and, at the same time, blame a recordset.

The second problem is that Microsoft has their own answer. Their answer can be reproduced but it does not involve a recordset.

The third problem is that the answer by Microsoft could very well contradict the first answer.

Here’s a not so often quoted link:-
https://groups.google.com/forum/?hl...soft.public.vb.general.discussion/rHI3KH-9lJ4
That link states that we shouldn’t even be calling it garbage collection, let alone closing, although I still do. (Oh well…)

So, all in all, I intend to advise not to close just prior to it going out of scope.
If you ever find a reproducible reason to do so then please advise Microsoft. I’m sure they would be all over it like a rash.

Chris.

Hi Galaxiom.

Thanks for jumping in and while the .NET is not the same as VBA you raise a good analogy.

Are you familiar with the iDisposable interface in .NET? this should be implemented by every class that uses system resources such as a recordset.

If you are familiar with it then I am sure you realise that in .NET all objects from these classes must be disposed/closed on all paths through the code otherwise you could end up leaking system resources. If you don't believe me try running code analyser.

Setting these to null before disposing will cause the same problem as allowing it to go out of scope without disposing, i.e. leaking system resources.

The .NET GC will free up memory from all objects when the last reference to it is set to null, or goes out of scope (in its own time).

This is why I say that you should close, and optionally set to Nothing. I choose to do both and unless someone can prove that this is wrong I will continue to - just like ChrisO will continue to not close/set to nothing until someone proves otherwise.

DrallocD.

>>This is why I say that you should close, and optionally set to Nothing. I choose to do both and unless someone can prove that this is wrong I will continue to - just like ChrisO will continue to not close/set to nothing until someone proves otherwise.<<

The question that was asked was about VBA and I think that logic is incorrect…


This is the point Eric Lippert is making in the link I posted.
We need a reason to write something.

Just because something does no harm does not mean it is okay to do it.
Nor does it mean that it is okay to advise others to do it.

Chris.

I optimized it for you



Obviously this is a silly example since strings are immutable and only use memory. The objects that need to be closed use other system resources. If a recordset holds a lock on a SQL table the lock will not be released until either a) it is closed b) the GC gets around to disposing it. I only have control over one of these so I close it.

Would you bother closing output files, or would you leave this to the GC too?

How about third party COM objects? Do you think the GC has a clue what resources (other than memory) they have open?

DrallocD.

>>Obviously this is a silly example<<
I’m glad you didn’t say that about my example.

>>since strings are immutable<<
Actually they are not immutable. String literals are immutable but strings per se are not.

>>and only use memory<<
Again, actually no. String literals may only be held in memory but strings per se can go to virtual memory which is held on disk.

>>The objects that need to be closed use other system resources.<<
Self evident fact and circular argument. All objects use system resources not just the ones that are supposed to need closing. Even if the objects reside in the cloud then the cloud becomes part of the system. The logic is faulty because what it is actually saying is that objects need to exist somewhere. Well, that’s a given but it does not address the subject if they need to be closed.

>>If a recordset holds a lock on a SQL table the lock will not be released until either…<<<
Incorrectly specified question. SQL is not a table it is a programming language.

>>Would you bother closing output files, or would you leave this to the GC too?<<
If by output files you mean things like text files on disk which are open with something like:-
Open BaseDirectory & "XRDTS.TXT" For Input As intFileIn
then yes, those files need to be closed. But, since you did not specify exactly what you mean it is impossible for me to comment further.

>>How about third party COM objects? Do you think the GC has a clue what resources (other than memory) they have open?<<
Obfuscation…
Third party COM objects, iDisposable interface in .NET, all paths through the code, leaking system resources, best practice, difference in performance, return a private backer, Workspaces, 10 million rows, active sessions, exposing it as a kind of singleton…

I think you might have missed one:-
“Synchronic exposure of multi-value, non-atomic tuples using the client-side cursor in a four dimensional space time continuum.”
(But don’t tell Sheldon.)

Please reread the first post in this thread; it’s a lot simpler than all that garbage.


Chris.

As I said in an earlier post, you will not change my mind and I will not change yours but I guess this may be entertaining for some readers.

By SQL tables, most database developers would know that I mean tables in a Microsoft SQL Server database. I appologise for making the assumption that you would know what I meant.

>>Actually they are not immutable. String literals are immutable but strings per se are not.<<
You might want to read this article on MSDN:
http://msdn.microsoft.com/en-us/library/ms234766(v=vs.80).aspx

Specifically the part that says:
"The Immutability of Strings
A string is immutable, which means its value cannot be changed once it has been created."

If you disagree with it, please take it up with Microsoft and not me!

>>Obfuscation…<<
All of the examples that I quote are problems that I have had to deal with in the past 12 months. They are not intended to be obscure examples aimed at confusing readers. You might want to reconsider your paragraph starting "Self evident fact and circular argument". You are deliberately confusing the resources that the GC can handle and those that it cannot and then go on to agree that the GC does not handle closing files.

>>“Synchronic exposure of multi-value, non-atomic tuples using the client-side cursor in a four dimensional space time continuum.”<<
Everyone agrees that server-side cursors work best in a four dimensional space time continuum - don't they?!

I reread the first post and I have to say that my advice is to close the database objects and set them to Nothing - would you agree?

DrallocD said:

I reread the first post and I have to say that my advice is to close the database objects and set them to Nothing - would you agree?

Click to expand...

Dralloc
I have been following this post since its beginning.
I am baffled by your arguments.
First of all the question is about Microsoft Access. Not SQL Server or any other product.
All you have done is to repeat the rhetoric of those before you. You have added nothing to the overall argument. I am now wondering if you even take the time to read the replies. I also doubt if you have taken the time to sufficiently examine the posted sample in order to give a qualified opinion. You appear never to have run any test as to memory leakage or the effects of Closing v Non Closing.
Do you have anything new to add to this argument in general. Can you post examples of memory leakage or any ill affect the non closing has within an Access Database.
Your final words at the time of my writing are quoted. Do you not understand the answer will be an emphatic NO. If you can’t understand that then how can you argue your point with any credibility?

I would have to say not removing objects can potentially make a huge difference. In the movies database I posted in the samples, I initially forgot:


These objects are regarding the file scripting object. The routine is a recursive routine, that can potentially run hundreds or thousands of times. Without the above, the routine would drag to a near halt. With it, it runs smoothly.

speakers_86

The only routine I can find with that in it is FillList but it’s not recursive.

The timing results with or without the setting to Nothing were the same.

Double clicking on the List Box:-
For populating the List Box with 233 entries it took 22875 +/- 50 milliseconds.

Maybe I’m testing it incorrectly?

Chris.

Firstly, using MS Access as a front end to SQL Server databases it common for many professional developers. I made reference to most of my VBA work was not against local tables in an earlier post.

Secondly, I have to admit guilt to your correct assumption that I did not run any of Chris' code samples on my computer. Proving something does not happen is not the same as proving that it can't happen.

Why is it my responsibility to prove the generally accepted best practice based on many developers experience over many years of experience? Since Chris can't prove that is can't happen his best option is to push the burden of proof on those who follow the best practice approach.

I have attached a zipped accdb with a form containing 2 buttons. One disposes and sets the values to nothing. The other does not. The one that does not leaks memory.

The title of the form tells you which PID to watch in the memory tab in Resource Monitor. You can get to Resource Monitor from the performance tab in Task Manager.