! Recordset Close !

[Wembly]

Hi,

I was wondering if it is good practice to close recordsets
as and when they are used.

For example, in a sub-procedure, we may have the following:

Set db = currentdb
Set rs1 = db.openrecordset("table", dbOpenDynaset)
rs1.findfirst Criteria1 & " and " & Criteria2
var1 = field1
var2 = field2
var3 = field3

rs1.close

Set rs2 = db.openrecordset("SELECT MAX(field7) FROM table
WHERE [ID] = CurrentID")
var4 = field4
var5 = field5

rs2.close

db.close

Are the two "close" statements necessary for better memory
management, or would the mere fact that when another
recordset has been SET will close the previously opened
recordset, as in, when rs2 (in the above example) is SET,
would rs1 be closed or would I need to explicitly close it
(as in the example)?

Thanks.
Wembly

 

[Marshall Barton]

I was wondering if it is good practice to close recordsets
as and when they are used.

For example, in a sub-procedure, we may have the following:

Set db = currentdb
Set rs1 = db.openrecordset("table", dbOpenDynaset)
rs1.findfirst Criteria1 & " and " & Criteria2
var1 = field1
var2 = field2
var3 = field3

rs1.close

Set rs2 = db.openrecordset("SELECT MAX(field7) FROM table
WHERE [ID] = CurrentID")
var4 = field4
var5 = field5

rs2.close

db.close

Are the two "close" statements necessary for better memory
management, or would the mere fact that when another
recordset has been SET will close the previously opened
recordset, as in, when rs2 (in the above example) is SET,
would rs1 be closed or would I need to explicitly close it
(as in the example)?

Most of the time a recordset will be closed when the rs
variables goes out of scope. The issue is the rare
circumstance when something goes wrong and the reference
count on the recordset doesn't get released. Another point
is that you maintain better control over the resources
consumed by the recordset if you release it as soon as it no
longer needed.

The general rule is that you should close what you open and
only what you open. You should also set to Nothing anything
you set. This means that Yes you should close the
recordsets and, since you did not open it, you should not
close the database (although, since this is such a common
mistake, Access ignores it). However, you should set all
three object variables to Nothing.

. . .
rs1.close : Set rs1 = Nothing
. . .
rs2.close : Set rs2 = Nothing
Set db = Nothing