Managing Recordsets (DAO) (1 Viewer)

[tmyers]

I have a question that I wasn't able to find a nice clear answer to that I am hoping you professionals could shed some light on.

When dealing with recordsets (DAO), when doing error handling if the event the module/process fails, should you apply .close and then set to Nothing?

If so, why? If not, also why? Should both methods be done, or is one sufficient? I know VBA has garbage collection but want to make sure I am at least attempting to do best practice.

For transparency, the method I think I should use would be something like this:

If Not <insert recordset variable here> is nothing then
    <variable again>.close
    Set <variable again> = Nothing
End If

 

[theDBguy]

I just use something like this.

errExit:
'cleanup
On Error Resume Next 
rs.Close 
Set rs = Nothing 
Exit Sub

 

[tmyers]

I just use something like this.

errExit:
'cleanup
On Error Resume Next
rs.Close
Set rs = Nothing
Exit Sub

But what would you do in the event you are working with multiple recordsets and an error occurs before one has been set/opened? It would cause another error when trying to close a recordset that was not yet opened would it not? This is the primary thought that raised this question as to what would happen if you were working with multiples and during an error, didnt close them as some may not have been set yet depending on where the error occurred.

 

[ebs17]

The question of closing a recordset as well as terminating the object variable does not arise for error handling alone, but in general for the procedure, no matter how it is completed.

I know VBA has garbage collection

If they can be relied upon, formally both actions would not be necessary.

In the past, however, there were sometimes problems with this, especially since a recordset is not a simple content like a number 123, but an access to tables with consequences, if one only considers the multi-user capability in the database.

In my eyes, therefore, the closing of the recordset is necessary in any case, as early as possible, but also in the case of a procedure abort due to errors. So this is done explicitly by own code.
The object variable as a pointer to a memory area is uncritical, it is only needed in its own VBA project or according to lifetime only in a single procedure.

 

[MajP]

Set objects to nothing very, very rarely does anything meaningful, but people will routinely act like if you do not do it then you are committing treason. There are instances where you should do it, but it is far better to learn those cases IMO then require every object to be set to nothing. People routinely claim "DAO has in the past had problems where if the RS was not set to nothing then the RS was not closed." That is absolutely a true statement... that occured in Access 95. So personally I am not worried about something fixed nearly 30 years ago. Here is a very good article. Do not confuse closing an RS with setting an object to nothing. Personally I almost never do either. And when I do, I do it purposely.

When Are You Required To Set Objects To Nothing?

learn.microsoft.com

 

[theDBguy]

But what would you do in the event you are working with multiple recordsets and an error occurs before one has been set/opened? It would cause another error when trying to close a recordset that was not yet opened would it not? This is the primary thought that raised this question as to what would happen if you were working with multiples and during an error, didnt close them as some may not have been set yet depending on where the error occurred.

For multiple recordsets, I do this:

errExit:
    'cleanup
    On Error Resume Next
    rs2.Close
    rs1.Close
    Set rs2 = Nothing
    Set rs1 = Nothing
    Exit Sub

errHandler:
    'do stuff like MsgBox
    Resume errExit

 

[theDBguy]

Set objects to nothing very, very rarely does anything meaningful, but people will routinely act like if you do not do it then you are committing treason. There are instances where you should do it, but it is far better to learn those cases IMO then require every object to be set to nothing. People routinely claim "DAO has in the past had problems where if the RS was not set to nothing then the RS was not closed." That is absolutely a true statement... that occured in Access 95. So personally I am not worried about something fixed nearly 30 years ago. Here is a very good article. Do not confuse closing an RS with setting an object to nothing. Personally I almost never do either. And when I do, I do it purposely.

When Are You Required To Set Objects To Nothing?

learn.microsoft.com

@MajP Thanks. I enjoyed the article, but I enjoyed reading the comments more.

I think they are right. I do it this way because it's been done that way ever since I learned it. It's similar to why I only use Long Integer variables now and never use Integers anymore. I had a bug one time where a calculation resulted in a number that was too large for an Integer variable that I just avoided it from then on.

Cheers!

 

[Josef P.]

But what would you do in the event you are working with multiple recordsets and an error occurs before one has been set/opened?

In which use case do you need open 2 recordsets in one procedure? (Don't say copy data from A to B )
Clear procedures = clear error handling

 

[The_Doc_Man]

A few years ago, we had a member of the Microsoft Office team who explained that there were circumstances where it would be a good idea to actually set an object to nothing. The circumstances were complex. There are times when you don't need to set it to nothing but closing any open object before exiting is always a good idea, particularly if you created something or altered something through that object. You want to force a flush of any memory buffers. If the object was opened solely to look up or get something but not modify the target of the object, then perhaps that flush is not needed.

IF the recordset object in question was declared in a subroutine called from an event subroutine (i.e. at least two stack layers in play), then the object is a stack context variable that will be erased and reclaimed when the 2nd level sub does an Exit Sub. You still should close it before leaving the sub, but it will cease to exist on the Exit Sub so you don't need to set it to Nothing.

Having said that, because I used to program in many languages, I always made it a point to close and dissolve objects explicitly, because not every language does that automatically and I didn't want to get into a bad habit because of VBA being forgiving.

Now, there IS a strategy that suggests that you should always close a recordset and immediately set it to nothing, but not because of any danger to the recordset. If you want to know if the recordset is open, NEVER EVER deviate from this:

Open the recordset. When you test the recordset variable, the NOT recordset IS NOTHING will say TRUE meaning the recordset is presumed open... BECAUSE when you close it, set it to Nothing and then your test of NOT recordset IS NOTHING will say FALSE and you will know it is closed. So in essence, to know if a recordset is open, you can use the IS NOTHING test and then never leave a recordset closed but not yet set to Nothing. This is a backdoor around the fact that there is no DAO recordset test for still being open that doesn't involve trying to manipulate the recordset variable to do something with it that looks like a recordset action.