1

If I have a query that I have created using VBA:

dim SQL as string
dim rs as recordset
dim db as database

SQL = "SELECT ... FROM ..."

Set db = CurrentDb
Set rs = db.OpenRecordset(SQL, dbOpenDynaset)

At the end of my sub I would always do the following:

rs.close
set rs = nothing

My question is, do I need to SQL ="" or something of that like? I think my confusion originally came from the fact that I haven't used set SQL in my code.

and if I do clear these strings, then, is there a 'best' way?

MiguelH
  • 1,415
  • 1
  • 18
  • 32
LiamH
  • 1,492
  • 3
  • 20
  • 34
  • No need to "clear" the strings, they will be destroyed at the end of the procedure. – Vincent G Oct 29 '15 at 15:30
  • In your code `rs` and `db` are objects that can remain in memory, so clearing them at the end is good practice. However 'SQL' is a simple string variable that only exists while the sub is being executed, so this does not need clearing in the same way. – MiguelH Oct 29 '15 at 15:31
  • thanks for the help. I shouldn't have to close db where it is my current db though right? – LiamH Oct 29 '15 at 15:34

1 Answers1

1

Since you're not opening a connection to either CurrentDb or the SQL string, there's no need to close them. However, you are opening a recordset, so that should be closed. It wouldn't harm anything to set SQL = "", but it's not going to actually do anything constructive.

As far as a "best way", I think you've already got it. At the end of your sub, or before any code that might prematurely exit it, just put:

rs.close
set rs = nothing
Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • To always do `set rs = nothing` is not necessary. Here is an extensive discussion: [Is there a need to set Objects to Nothing inside VBA Functions](http://stackoverflow.com/questions/517006/is-there-a-need-to-set-objects-to-nothing-inside-vba-functions), and here an interesting read: [When Are You Required To Set Objects To Nothing?](http://blogs.msdn.com/b/ericlippert/archive/2004/04/28/when-are-you-required-to-set-objects-to-nothing.aspx) @LiamH – Andre Oct 29 '15 at 16:07
  • @Andre451 - That first link does actually say it's good practice, because Access' garbage collecting is unreliable. That being said, I recently took over an application that ran just fine for 10 years prior to my involvement, and the previous coder never closed **any** connections. So, is it necessary? No. Is it good practice? Yes. – Johnny Bones Oct 29 '15 at 16:13