recordsets and closing connection 1

[1DMF]

Hi,

If I have a variable rs declared as a DAO.recordset and open a recordset.

If I then forget to issue an rs.Close command, but Set rs = Nothing, does this also close the connection, or could this leave open redundant connections to the SQL server even though the recordset object is garbage collected?

Thanks,

1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[MajP]

It closes the connection. See the last line.

http://msdn.microsoft.com/en-us/library/bb243098.aspx

 

[1DMF]

Thanks MajP.

I have added this code to my classes to be on the safe side, and so far no-one has complained about any runtime errors trying to close an already closed recordset.

Private Sub Class_Terminate()

    ' close recordsets and kill all objects
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If

    ...
    
End Sub

Though not sure if I should remove the close command, if setting it to Nothing closes an open recordset this would remove the risk of a potential run-time error with the Close method?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[Andrzejek]

I use ADO so I am not sure if this applies to DAO, but try - to be on safe side:

If Not rs Is Nothing Then
    [blue]If rs.State = 1 Then[/blue] rs.Close
    Set rs = Nothing
End If

Have fun.

---- Andy

 

[MajP]

That works in ADO, but there is no way in DAO to determine if the RS is open. So you can just close it and either trap the possible error or put a resume next.

With that said I do not believe either is needed or does anything. However if someone can explain, I am interested. The recommendation always states that "It is a good habit" or "In older versions of access". So I think all of the closing of recordsets and setting to nothing is just "old habits". It may be a good habit if your coding in other languages or working in very old versions of access. I always see "good habit", but yet to see an explanation of what will happen if you do not.

Yes, in earlier versions of Access (97 and prior) there was a problem with DAO recordsets. As far as I know this was a DAO issue and not a general VBA issue. This was a bug. Basically you could get a hanging reference and the recordset would not close. So thus the need to explicitly close it and set to nothing. AKAIK this was fixed a long time ago.

I do not believe any such problem exists anymore and do not think it "could happen". I believe now a days that without any code your procedure will end, the variable will go out of scope, the reference counter will decrement, and if it goes to zero the structure is destroyed and the memory released closing the connection.

In VBA I never set variables to nothing and never close DAO recordsets. I am not recommending that others do that, just saying I am not convinced of the need. But am interested if someone disagrees and has some information to support it.

 

[1DMF]

Apparently there isn't a 'State' property with DAO.

I have seen suggestions that you could do the following

If Not rs Is Nothing Then
    On Error Resume Next
    rs.Close
    Set rs = Nothing
End If

But I'm not keen on overriding my default error behaviour and then having to put it back?

On Error Goto err_my_sub

If Not rs Is Nothing Then
    On Error Resume Next
    rs.Close
    Set rs = Nothing
End If 

On Error Goto err_my_sub

Think I'll just remove the .Close as Nothing will do it for me!


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads