CODEWITHSUNDEEP
vbams-accessrecordset
user3112028
user3112028

Reputation: 21

DAO Recordsets: Should I close and set to nothing if I am reusing the same name over and over?

Conceptually, which is better regarding memory leak and best practice:

Do
 set mTable = "something"
 'do a bunch of stuff
 mTable.Close
 Set mTable = nothing
Loop

Or:

Do
 set mTable = "something"
 'do a bunch of stuff
Loop
mTable.Close
Set mTable = nothing

Is one faster that the other?

Upvotes: 2

Views: 602

Answers (1)

Erik A
Erik A

Reputation: 32642

You forgot the choice of my preference: 

Do
     set mTable = "something"
     'do a bunch of stuff
     mTable.Close
Loop
Set mTable = nothing

mTable.Close commits the transaction, and you might run into troubles with too many nested transactions when running a large number of add or edit actions on the recordset (especially when working with attachments and multi-valued fields, which should be avoided if possible).

Set mTable = Nothing clears the reference to a closed recordset, and there's not really a reason to use this inside a loop, because you're reassigning something else to it after a couple of milliseconds.

It all depends on what you're doing exactly. As @Gustav stated, the last one is mostly fine. However, when editing and inserting, I prefer my option.

Upvotes: 2

Related Questions