Setting Objects to Nothing Problem 3

[gordonl]

I am trying my best to set all the objects I use to Nothing when a form unloads. The objects I use are ADO recorsets and ADO commands.

My problem is sometimes an object is not iniated using the SET statement so when I come to close it I get an error.

My solution is just to SET it again as a NEW occurance of the object and then set it to nothing. However, my question is what if IT HAS been SET before - does resetting clear all the resources nicely or are they left hanging in the computers memory somewhere.

An example would be:

DIM rstemp as ADODB.RECORDSET

SET rstemp as NEW ADODB.RECORDSET
- load up the recordset with data -

SET rstemp as NEW ADODB.RECORDSET
SET rstemp=NOTHING

You will see that I have created a new instance of rstemp (2 in total) but set it to Nothing once. What happens to the original?

 

[Slugmaster]

The original should be automatically discarded (set to nothing) when you create the second object. This happens "behind the scenes" and you should not have to explicitly do it.

One good reason to do it explicitly is that you know exactly when (in your flow of code) the object is released.

 

[jmarler]

"My solution is just to SET it again as a NEW occurance of the object and then set it to nothing."


There really is no point to creating an object with a new command ONLY to set it to nothing. This is only going to be wasted time in your application. Just set the object variable to nothing . . . it won't matter if it is already nothing. If you are tring to call methods on the newly created object, remember that it is a NEW instance of the object and will have no effect on the original object. - Jeff Marler B-)

 

[gordonl]

Thanks for your replies.

Jeff, you're right I can just set something to nothing even if it has never been set. I never thought of that. I still may have a problem though - if it is a recordset object that may have or may not have been set.

If I just set it to nothing when it Has been Set and Opened then I have been working to a rule that I .close the recordset before setting it to nothing.

Therefore my next question is what happens if I set a recordset object to nothing before executing its close method. Is there resources left in the computer that cannot be freed?

 

[chiph]

gordonl -

We do code like this to cleanup:
[tt]
If Not adoRS Is Nothing Then
If adoRS.State = adStateOpen Then
adoRS.Close
End If
Set adoRS = Nothing
End If

Set adoComm = Nothing

If Not adoConn Is Nothing Then
If adoConn.State = adStateOpen Then
adoConn.Close
End If
Set adoConn = Nothing
End If
[/tt]
So we set the variables equal to nothing in the reverse order in which they were created -- recordset, command object, and ADO connection object.

Chip H.

 

[gordonl]

chiph

Thanks that is fantastic - exactly what I wanted.

I had been trying to find a method that could tell me if a recordset had been SET, but if it hadn't been and I tried to refernce it I got an error.

I then tried the IsEmpty command but it says it only works with strings and integers.

Your way seems ideal - I am away to test it now.

Thanks again.

 

[jmarler]

What Chip Suggested will work prefectly for you. As an aside, you can use the Is Nothing test to check ANY object type to see if it has been set or not. - Jeff Marler B-)