How to check for an open recordset?

[vbaInet]

Close clearly goes about an orderly routine while Nothing just drops the object. Obviously the Copy would be deleted when it was set to Nothing but who knows what else might be left lying around.

It sure does. What you will even find more interesting is that if you close the "Copy" object it doesn't affect the main copy.

While some might continue to debate on recordsets I have no doubt that Close absolutely should be used for some objects.

In the case of a Connection object no doubt the Close would negotiate a shutdown of communication with the other object while Nothing would leave the connection hanging without the other end knowing, ultimately closing their end on timeout.

Absolutely, whereever a Close action is possible on a connection object, use it. It would seem that the Close action is more significant than setting it to Nothing for connection objects.

You will find this even more interesting:

Public Function TestDictPointer()
    Dim objMain As Dictionary, objCopy1 As Dictionary, objCopy2 As Dictionary
    
    Set objMain = New Dictionary
    
    objMain.Add "A1", "something"
    objMain.Add "B2", "something"
    objMain.Add "C3", "something"
    
    Set objCopy1 = objMain
    Set objCopy2 = objCopy1

'    Set objMain = Nothing
    Set objCopy1 = Nothing
'    Set objCopy2 = Nothing

    Debug.Print "Main pointer: " & ObjPtr(objMain) & vbTab &  vbTab & "Main Is Nothing? " & (objMain Is Nothing)
    Debug.Print "Copy 1 pointer: " & ObjPtr(objCopy1) & vbTab  & vbTab & "Copy 1 Is Nothing? " & (objCopy1 Is Nothing)
    Debug.Print "Copy 2 pointer: " & ObjPtr(objCopy2) & vbTab  & vbTab & "Copy 2 Is Nothing? " & (objCopy2 Is Nothing)
'    Debug.Print "Copy 2 count: " & objCopy2.Count
End Function

Notice Copy2 debug.print line. Uncomment the = Nothing one by one but only have one = Nothing uncommented at each run.

You will notice that setting Copy1 to nothing has no effect on Copy2. But setting Main to nothing drops both Copy1 and Copy2.

So another learning outcome is to always Close the originator object and/or set it to Nothing (where and is for connection objects, or refers to connection-less objects). Plus always set all types of objects to Nothing.

I would imagine this same principle would apply to creating new instances of a form or report object.

 

[ChrisO]

I believe the code in post #16 is an incorrect interpretation of the basic premise which has been going on for far too long.

Basic premise 0… (That has been going on for far too long!)
You should always close objects and set them to nothing because the garbage collector is broken and if you don’t do it manually Access will leak memory out of the sides of your computer and eat your first born.
Rubbish! It has never, to my knowledge, been proved and I am still waiting for a single instance of proof.


Basic premise refined 1…
You should always close objects and set them to nothing because the garbage collector might not do so correctly.
Rubbish! It has never, to my knowledge, been proved and I am still waiting for a single instance of proof.


Basic premise refined 2…
You should always close local objects and set them to nothing because the garbage collector might not do so correctly.
Rubbish! It has never, to my knowledge, been proved and I am still waiting for a single instance of proof.


The code in post #16 proves that there is a difference between closing and setting to nothing.
Of course there is a difference between closing and setting to nothing else one of them would be redundant.

It does not prove Basic premise 0, 1 or 2.
----------
If it proved basic premise 0 then my test of it should have caused some reproducible failure, and it didn’t. It did not leak memory and my first born is still alive.
All it did was cause a reproducible error which it should have because it was designed to do so.
----------

----------
If it proved basic premise 1 then the might part of the premise should only increase the mean time between failure. We should therefore ask Microsoft what the MTBF of DAO or ADO is, but don’t expect an answer. I have never seen any reproducible evidence so went about trying to reproduce it. With a friend of mine we set about trying to reproduce memory leakage. I wrote some of the worst case code I could think of using DAO. We split the database and ran it across my friend’s network. Millions of loops, hours to complete and no, nothing, zilch in the way of memory leakage. It was written in Access 2003 so we pushed it back to Access 97 and did a rerun. No, nothing, zilch in the way of memory leakage. We posted it on UA and one of the members there pushed it back to Access 2.0. No, nothing, zilch in the way of memory leakage.

Three things come to mind. 1, no one has posted a single instance of evidence. 2, we, and someone from UA, could not reproduce it in Access 2003, Access 97 or even in Access 2.0. 3, Microsoft is not likely to publish the MTBF of DAO and that is probably because it doesn’t exist.
----------

----------
It can’t prove basic premise 2 because the object in question is global.
It is not the intention of garbage collection to clean up globals of any kind.
Imagine if garbage collection within a procedure cleaned up globals...

Dear gurus of Access World Forums,
I have this function to set a global variable, and it sets it, but when I exit the function it is not set.
What’s going on???

Dear confused,
That’s what exiting a function does; the function’s garbage collector resets all globals to their original state.

Dear gurus of Access World Forums,
Thank you for your answer but what should I do???

Dear confused,
You have three choices…
1. Don’t use global variables. (It’s been said before, just don’t use them. )
2. Don’t exit the function. (This is one of the reasons a function does not need a return value, you shouldn’t exit functions. )
3. Find another site for advice.
…

Dear confused,
Are you still there, hello…?
----------

And so to the point of this discourse…
I do not think it is correct to extrapolate the above to: -
>>So another learning outcome is to always Close the originator object and/or set it to Nothing (where and is for connection objects, or refers to connection-less objects). Plus always set all types of objects to Nothing.<<


*****************************

>>I would imagine this same principle would apply to creating new instances of a form or report object.<<

In a sense the above statement is correct but we differ on what is meant by ‘same principle’.

My interpretation of ‘same principle’ is…
Rubbish! It has never, to my knowledge, been proved and I am still waiting for a single instance of proof.

The evidence I would put forward is that people create instances of Forms and Reports all the time. They may not know it but they do.

Take for example a sub-form; is it the Form or an instance of the Form.
It’s not the Form but an instance of the Form. Look at the Forms collection when a sub-form is open; is the Form in the collection? No, and why? Because it is not open but an instance of it is and it has the same name.

Now for the questions;
How many people go about manually destroying the instance of a Form when used as a sub-form?
None!

But a Form is a complex object; does it not cause Access to leak memory out the sides of my computer and eat my first born?
No!

Why?

Dear confused,
You have three choices…

I hope nobody gets offended by this discourse; it’s just Sunday humour.

Chris.

 

[Banana]

What I don't understand is why raise an error

given the code

If Nz(some_field, "") then err.Raise 94, etc.
then why not just

If Nz(some_field, "") then
goto some label
end if
or is raising an error "slicker" in some way.

With an error you have more flexibility with how to resolve the error. If it is appropriate, you can invoke "Resume" to return to the offending statement and re-try it again. Likewise you can use "Resume Next" if you can safely continue on the next statement, or you can exit the procedure. You can't do those with a GoTo and while in theory, you could just add a label so the "some label" can then return, you're stuck with only one place - nobody wants to add label for every possible points in procedure but using Resume/Resume Next let you get back to business without such labeling.