Proper Practice with Regard to Closing Recordsets (1 Viewer)

shadow9449

Registered User.
Local time
Today, 17:25
Joined
Mar 5, 2004
Messages
1,037
I have some forms that have unbound areas that draw from different recordsets (tables, queries and so on). In order to populate these areas, I call the respective recordsets, draw the data and then close the recordset.

Here's some pseudo-code to show how I do it:

Code:
Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb
Set rs = db.OpenRecordset({My first recordset to open})

If rs.RecordCount > 0 Then
  {show the data on the section of the form}
End if

Set rs = db.OpenRecordset({My second recordset to open})
If rs.RecordCount > 0 Then
  {show the data on the section of the form}
End if

and so on...

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

It's clear from everything that I've read that you should close the recordset and database that you opened and then set them to nothing as I have in the last section of the code. BUT note that in the course of execution, rs will be set to many different recordsets before I close it at the end.

My question is if it would be better practice to close rs EVERY time I've drawn data before I set it to another recordset? I don't know enough about Access' memory management to know how this is best done.

As an aside, I used to rely on Dlookup for the same task until I've read about how much more efficient using DAO is and I would guess that many people still do this.

Thank you

SHADOW
 

boblarson

Smeghead
Local time
Today, 14:25
Joined
Jan 12, 2001
Messages
32,059
1. No, it isn't necessary to close them before reassigning, but I typically do just as a clean way to delineate that happening in my code.

2. You really don't have to worry about cleaning up after them, although again it is just kind of a way to make your code more self-documenting as to what is happening. Access/Jet is good about that clean up now. It isn't like it was many years ago when we suffered memory leaks because of not closing the recordsets and destroying the variables. (I've been told this by Brent Spaulding - datAdrenaline on this forum, if I remember correctly and he's good with this stuff)
 

shadow9449

Registered User.
Local time
Today, 17:25
Joined
Mar 5, 2004
Messages
1,037
Hi Bob

First, welcome back :D

1. No, it isn't necessary to close them before reassigning, but I typically do just as a clean way to delineate that happening in my code.

2. You really don't have to worry about cleaning up after them, although again it is just kind of a way to make your code more self-documenting as to what is happening. Access/Jet is good about that clean up now. It isn't like it was many years ago when we suffered memory leaks because of not closing the recordsets and destroying the variables. (I've been told this by Brent Spaulding - datAdrenaline on this forum, if I remember correctly and he's good with this stuff)

Based on part 2 of your answer, it would seem that closing the recordsets does not help with memory management. My impression that they had to be closed was based on a KB article on MS (could be old) and Allen Browne's Elookup() documentation and possibly other sources.

When you say that it's not necessary any more, is this because more recent versions of Access are more efficient (if so, would this be true in Access 2000 - 2003) or because the operating systems are more efficient or some other reason?

Thanks!

SHADOW
 

boblarson

Smeghead
Local time
Today, 14:25
Joined
Jan 12, 2001
Messages
32,059
It isn't that the OS is more efficient. It is the garbage collection in Access has gotten better. I believe (and I could be wrong about this) that anything 2002 and above should be fine (and possibly 2000, but I am not sure about that).
 

shadow9449

Registered User.
Local time
Today, 17:25
Joined
Mar 5, 2004
Messages
1,037
It isn't that the OS is more efficient. It is the garbage collection in Access has gotten better. I believe (and I could be wrong about this) that anything 2002 and above should be fine (and possibly 2000, but I am not sure about that).

Excellent information...thank you.

SHADOW
 

Users who are viewing this thread

Top Bottom