12
Dim rs as ADODB.Recordset
set rs = ReturnARecordset 'assume ReturnARecordset does just that...

'do something with rs

rs.Close
set rs = Nothing

Is it necessary to call rs.Close before setting it to nothing?

Edit: We have one global connection that we keep open for the duration of the application and all recordset objects use this same connection. I see two answers below talking about the need to close recordsets in order to ensure connections aren't left hanging open. To me that sounds like a lot of silly talk because connections are controlled with connection objects, not recordset objects right? But please let me know if I'm missing something here...

feetwet
  • 3,248
  • 7
  • 46
  • 84
Brandon Moore
  • 8,590
  • 15
  • 65
  • 120

4 Answers4

7

The only reason calling Close explicitly is when you are not sure if the recordset is referenced from somewhere else in your project, usually a result of some sloppy coding.

Dim rs as ADODB.Recordset
Set rs = ReturnARecordset
...
MyControl.ObscureMethod rs
...
Set rs = Nothing

Last line is supposed to terminate the recordset instance without calling Close explicitly, unless MyControl is holding an extra reference and thus preventing normal tear-down. Calling Close on rs will make sure MyControl cannot use its reference for anything useful, crashing in flames in the meantime.

wqw
  • 11,771
  • 1
  • 33
  • 41
  • My thoughts exactly. I'm mostly trying to figure out if there's a valid reason to call the Close method on recordset variables that are private to the function they were created in and quickly go out of scope. – Brandon Moore Dec 06 '11 at 01:53
  • 4
    Calling `Close`, explicitly setting *local* references to `Nothing` are parts of the same Cargo Cult programming that internet bathroom of code is full of. Use common sense, best would be to test for leaks in your particular environment. – wqw Dec 06 '11 at 09:05
  • Yeah, I don't ever set to nothing when I know it's about to go out of scope anyway. But I didn't want to hear multiple answers on the importance of setting it to nothing when that wasn't what my question was about so I put the line in there to prevent those comments :) – Brandon Moore Dec 06 '11 at 09:44
4

Yes, this does more than just force a garbage collection it also tells the server the connection is being terminated, this avoids having multiple open orphaned connections (they will eventually time-out by themselves) but its always best practise to close them out.

This is especially apparent when ADODB is using a remote connection rather than a local one.

John Mitchell
  • 9,653
  • 9
  • 57
  • 91
  • 1
    That doesn't make sense. Recordset.Open takes a connection object as a parameter and closing the recordset doesn't close the connection. Our application has a single connection object that stays open for the duration of the application and all queries use this object. So with that in mind, is there any other reason to close the recordset? I would assume that when the recordset's reference count reaches 0 it will take care of anything calling Close would do in its destructor but I'm not sure so that's why I was asking. – Brandon Moore Dec 05 '11 at 06:01
  • That is, is there any other reason to close a recordset when it's going to be immediately set to nothing afterward (either explicitly or by going out of scope) – Brandon Moore Dec 05 '11 at 06:16
2

I needed to write many files. If I didn't close the connection after each file written then I got extraneous garbage at the end of subsequent files.

fsT.Close

followed by

fsT.Open

to "refresh" the output stream. So when I saved a new file, it was "clean".

Roberto Caboni
  • 7,252
  • 10
  • 25
  • 39
gary
  • 21
  • 2
1

You can run into ODBC or OLEDB Pooling issues, which keep a connection open and tie up a pool slot:

Common causes of connection creep include:

The ADO Connection and Recordset objects are not actually closed. If you don't explicitly close them, they won't be released into the pool. This is probably the single most frequent cause of connection creep.

An ADO object you created (especially a Connection object) was not explicitly released. Explicitly releasing objects you create is just good programming practice. If you allocate memory, release it. Depending on the language you are using, letting a variable go out of scope may or may not result in it being released.

See Pooling in the Microsoft Data Access Components

And if there is any chance of .Net Interop involved be wary: there are lots of warnings about problems caused due to the lazy way COM object (or contained object) release occurs under .Net's garbage collection.

Bob77
  • 13,167
  • 1
  • 29
  • 37
  • 1
    Pooling is not an issue as far as I know when a Recordset was not opened with its own Connection. – Bob77 Dec 06 '11 at 21:36
  • Okay that makes sense. I have only been working with recordsets that have been passed a connection object and honestly didn't even remember that you could open them without doing so. Thanks. – Brandon Moore Dec 07 '11 at 01:45