garbage collection in vba

is there any garbage collection in vba that would dispose all used code reference.  I would like to add this to my close button.
vbnetcoderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
No, VBA has no such capability.  Clean-up is automatic, but as anyone that has worked with VBA for any length of time will tell you, you always:

1. Use option explicit
2. Close anything you open
3. Set the object variable to nothing

 and not leave it up to VBA.

Jim.
1
vbnetcoderAuthor Commented:
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset(strSelect)
rs.Close

In this code should i add

set rs = nothing

?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Yes, or as I like to do:


Public Sub....

  Dim db As Dao.Database
  Dim rs As dao.Recordset

  On Error Goto Errror_Procedure

  Set db = CurrentDB()
  Set rs = db.OpenRecordset(strSelect)
 
Exit_Procedure:
  On Error Resume Next
 
  rs.Close
  Set rs = nothing

  Set db = nothing

  Exit Sub

Error_Procedure:
    ' Error handler here
    Resume Exit_Procedure
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"2. Close anything you open"
Just as side note ... note the work 'open'

Do not Close CurrentDB ... because CurrentDB is the instance of Access that is already open.


So ... no

Dim db as DAO.Database

Set db = CurrendDB

... code

db.Close  ' Don't do this

Set db =  Nothing' Do this
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Just to add a bit to what Joe said, you don't need to close it, but if you do it by mistake, there are no dire consequences.

 A db.close results in a no-op.

Jim.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
However ... in days gone by ... circa Access 95-97 ... some subtle errors *could* occur if you did close CurrentDB (per Michael Kaplan) ... but in later versions that was corrected.

So, for best practice ... follow the rule Jim noted:
"If you didn't Explicitly open it., don't close it"

mx
0
aikimarkCommented:
Code is always (supposed to be) cleaned by the VB run-time environment up when you leave a routine.  This clean-up only applies to local variables.  If you have module/form level variables, they will not be cleaned up until the entire application ends.

The (supposed to be) is very important, since it doesn't always happen as expected.
0
Jeffrey CoachmanMIS LiasonCommented:
vbnetcoder,
Off topic here, ...I only mention this because I know ho much you have been digging into Access here lately.
;-)

A utility like this will identify all unused code and variables

<No points wanted here>

JeffCoachman
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and you can do the same thing with Find And Replace (www.rickworld.com) if you don't want to spring for the FMS product.

Jim.
0
Jeffrey CoachmanMIS LiasonCommented:
...forgot the Rick's had that as well....
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
It is only one level deep though....you need to run it once, identify everything and delete, then run again.   Not a big deal, but I'm not sure if the FMS tool goes further or not.

 But heck, for $30, you really can't go wrong.  The actual Find and Replace alone is well worth it.

Jim.
0
vbnetcoderAuthor Commented:
ty
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.