Closing Database Objects: Explicitly Required?

[DrallocD]

Okay, yes - I realised that they were quoting VB, not VBA but the definitions are correct for both in what they do. I noticed that the default was wrong for VBA so I corrected the option explicit post to avoid causing any confusion.

MSDN does not cover VBA very well and I did not want to quote random forum posts since these are easily discounted as "spouting the old rhetoric of others"

I appologise to anyone upset by this.

Now can we move on? I will use no more references to VB rather than VBA (even where they are the same).

 

[ChrisO]

DrallocD.

Thanks for your reply.
Now we have one indisputable fact on which to work; everyone can make mistakes.

And yes, now we can move on.

Please bear in mind the original question in post #1 because I think the original poster may have been forgotten.

Chris.

 

[DrallocD]

Hi ChrisO,

I am hoping that this is the new "more constructive and possitive" version of this thread.

Did you want to proceed to point 2 ?:
>>Dim c1 As New Class1<<
>>is not the same as:-<<
>>Dim c1 As Class1<<
>>Set c1 = New Class1<<

>>If the Private Sub Class_Initialize() is not called then neither will Private Sub Class_Terminate().<<

This is easilly tested:

in the sample I sent you, add the following lines of code to class1 and class2:

Public Sub Class_Initialize()
Stop
End Sub
Public Sub Class_Terminate()
Stop
End Sub

Then, re-run the test and let me know if the execution stops on these lines as it does with mine. If not, I will test with 2003 and send you a new version of the database.

 

[ChrisO]

DrallocD

That has nothing to do with the original question in this thread.



This is what I originally said in post #28:-
----------
Secondly:-
Dim c1 As New Class1
is not the same as:-
Dim c1 As Class1
Set c1 = New Class1

Unless the Class is called otherwise, simply using:-
Dim c1 As New Class1
will not call the Private Sub Class_Initialize().
If the Private Sub Class_Initialize() is not called then neither will Private Sub Class_Terminate().

If :-
Dim c1 As Class1
Set c1 = New Class1
is used, both the Private Sub Class_Initialize() and Private Sub Class_Terminate() are called, even if no other call to the Class is made, except as below.
----------


And this is how you choose to quote me:-
----------
>>Dim c1 As New Class1<<
>>is not the same as:-<<
>>Dim c1 As Class1<<
>>Set c1 = New Class1<<

>>If the Private Sub Class_Initialize() is not called then neither will
>>Private Sub Class_Terminate().<<
----------


So test your code without calling the Class in any other way.
I had already done so prior to making the statement in post #28.

And please, try not to misquote me.



Chris.

 

[DrallocD]

Okay, I appologize for the misquote - but since neither the Class_Initialize, not the Class_Terminate were part of my sample code, and that Class_Initialize would have been called in my sample code had it existed, can we proceed?

I am happy to talk about point 3, or move to point 4 if you prefer?

 

[ChrisO]

DrallocD

>>I am happy to talk about point 3, or move to point 4 if you prefer?<<
Not yet.

If I may quote myself from post #13:-
>>We need a reason to write something.<<

So I think your next question should have been:-
Chris, why did you introduce Private Sub Class_Initialize() and Private Sub Class_Terminate() into the discussion?

Chris.

 

[DrallocD]

Okay, I think this may also cover point 3 but if not we can cover it later. I think I already saw where you are going with this but I should not have presumed so:

Chris, why did you introduce Private Sub Class_Initialize() and Private Sub Class_Terminate() into the discussion?

 

[ChrisO]

DrallocD

I introduced Private Sub Class_Initialize() and Private Sub Class_Terminate() into the discussion in order to test your code.

As a matter of KISS, one Form and one Class module…

Behind the Form:-

Option Explicit
Option Compare Text


Private Sub cmdTestIt_Click()
    Dim c1 As New Class1
   
    c1.SetX 123
  [color=green]' c1.remember1 c1     ' Uncomment to see the difference.[/color]

  [color=green]' Set c1 = Nothing    ' Uncomment if you like; it does nothing.[/color]
    
End Sub

In the Class module:-

Option Explicit
Option Compare Text

Private X               As Long
Private remember1backer As Variant


Private Sub Class_Initialize()
    MsgBox "In Class_Initialize"
End Sub

Public Sub SetX(ByVal lngValue As Long)
    MsgBox "In SetX"
    X = lngValue
End Sub

Public Sub remember1(ByRef o As Variant)
    MsgBox "In remember1"
    Set remember1backer = o
End Sub

Private Sub Class_Terminate()
    MsgBox "In Class_Terminate"
End Sub

If you can see the difference, can you explain it?

Access 2003 demo attached.


Chris.

 

[DrallocD]

Yes, I can explain it.

The purpose of my test is to show what happens when you try to dispose a class that the GC can't handle (either a self reference or a cyclical reference in this case). Your example contains a Long type which is held internally on the stack rather than an object which is held on the heap and therefore much easier to dispose by the GC.

In my example, the class_initialize is called for both Class1 and Class2 as the classes are initialized, as you would expect. The difference is that the Class_Terminate can't be called until the last reference of an object is destroyed which is not possible in the case of a self reference and cyclical reference unless the property or field causing the self/cyclical reference is set to nothing first.

The class_terminate is called on the button that does not leak memory as it sets the self & cyclical references to Nothing before closing the object.

The reason for this is down to how MS Access holds objects. Variables actually hold pointers to objects and it is not until the last pointer is removed that the object is removed by the GC. In the case of self referenced objects (where object a has a pointer to object a), or cyclical references (where object a point to object b and object b points to object a) that the GC is unable to dispose the object.

In my example, since object a pointed to both object a and b and object b pointed to both object a and object b the GC could not remove either. For this reason the class_terminate would not be run on either object a nor b for the button which caused the memory leak.

This sample code was to show that setting the value to Nothing at the level at which it was set is essential to safe Access programming. This is usually done in the Close() or Dispose() methods but is not possible in the Class_Terminate as this demonstrated.

This also explains point 3 which is that the last reference to an object is the one that calls Class_Terminate, not everytime you set a pointer to it to nothing. This is why it is impossible for it to get into an endless loop.

 

[RainLover]

Rather than getting too involved along the current lines of this thread, I would draw your attention to whdyck’s original question in post number one.

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.

I have attached a Demo Database that tests for memory leakage.
In writing this I called upon Chris for his help not only in writing but also testing.
This Database opens a Recordset and then updates 1,000 records in a Table. It is in a loop so that it can be run as many times as one would like. The Default is 500 loops.
There is no Closing of the Recordset other than what is done by “Exit Sub”
There is no sign of Memory leakage.

 

[DrallocD]

Thanks RainLover, but this has moved on from the original question as ChrisO has brought in the advice that you never need to set a value to Nothing and never need to close a Recordset. If he is okay with retracting this advice then the thread is closed but I somehow doubt that this is the case. Besides, it is an interesting disussion so you should stay tuned.

I for one am interested in the outome. maybe it will change the way that I, or Chris will write code in the future!

 

[RainLover]

Thanks RainLover, but this has moved on from the original question as ChrisO has brought in the advice that you never need to set a value to Nothing and never need to close a Recordset. If he is okay with retracting this advice then the thread is closed but I somehow doubt that this is the case. Besides, it is an interesting disussion so you should stay tuned.

I for one am interested in the outome. maybe it will change the way that I, or Chris will write code in the future!

Dralloc
I posted the Demo with you in mind. You have spoken about Memory Leakage in 5 or 6 posts.

All of a sudden you are no longer interested.

Did ChrisO say "Never". I will go back and have a look.

 

[RainLover]

ChrisO has brought in the advice that you never need to set a value to Nothing and never need to close a Recordset.

Dralloc

I went through Chris's post and I cannot find where he said never.

Could you please point it out to me as I would like to see the context of the statement.

 

[DrallocD]

Thanks Rainlover, I did look at the example that you posted but as I have said in the past - there is a big difference between does not happen and can not happen. If you want to prove something, it taked infinately more evidence than to disprove something which takes only one example. In the case of closing a recordset vs not closing it I will address later. We are still covering setting values to Nothing.

 

[DrallocD]

@ rainlover:
The post was in #3 (his first so not that hard to find) where he said:
>>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.

I hope this clears this point up for you.

 

[RainLover]

Dralloc

That is not Chris's quote.

You must be getting confused. Could you please have another look and show me this statement by Chris where he said never.

 

[DrallocD]

Hi RainLover,

You are correct that Chris did not say never, it was more implicit than explicit in his reply.

This comment was cut and pasted from post number 3 which was posted by Chris. I am not sure why you say that he didn't say this but if that is the case there is also post 26 where Chris says: "This discussion is about the need to set objects to Nothing."

You could have quoted my comment from post 12 in which I said "This is why I say that you should close, and optionally set to Nothing" as an alternative point.

Since always setting to Nothing will never cause a bug and forgetting to when needed will, I err on the side of always setting them to Nothing but I accept that in many places it is not needed. This is why I said that it is best practice.

This is similar to the argument, should I use:

Dim s as String
Dim b as Boolean
s = vbNullString
b = False

or

Dim result as Boolean
If x.enabled = true then 
   result = true
Else
   result  = false
Endif
Return result

...which I personally hate but I would never criticize anyone for writing (okay the second one I would!)

I don't mind parking setting values to Nothing and moving on to rs.Close if this is a moot point but that would be up to Chris.

 

[RainLover]

Dralloc

I am not sure why you say that he didn't say this

Why do you insist on misquoting. I did not say that he did not say never. That is what you said. I simply asked for you to point out where you claim he did.

I am not going to say that Chris said something when he didn't. I will leave that sort of thing up to you as you are very good at it.

 

[DrallocD]

Hi RainLover,

Sorry, but I am finding it difficult to follow what you are saying.

Rather than me guess and risk misquoting someone again, can you please clarify what you meant by : "That is not Chris's quote." or reword your question / point?

 

[RainLover]

The follow is Chris quoting the OP from post number 1.

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