Solved

Access Functions - Best way to clean up variables at end of function

Posted on 2003-11-27
5
332 Views
Last Modified: 2006-11-17
At the end of a function in VBA what is the "best way" to clean up variables?

1.  Close each object only.

MsSet.close
MyDB.close

2.  Set each object to nothing only.

Set MySet = Nothing
Set MyDb  = Nothing

3. Set ONLY the DB variable to Nothing, even though other variables have been used.

Set MyDB = Nothing

4. Close all variables and then set to nothing

MsSet.close
MyDB.close

Set MySet = Nothing
Set MyDb  = Nothing


Please also tell me why you are suggesting an option.

Thanks.




0
Comment
Question by:hertzgordman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 9832736
Hey hertzgordman!

  I do it this way
  rs.close: set rs = nothing
  db.close: set db = nothing

I always try to destroy the objects from the child level up.

regards
Jack
0
 

Author Comment

by:hertzgordman
ID: 9832768
I like your approach.

But please tell me that would be the consequence of NOT setting the objects to nothing.

I have a whole bunch of functions where I am NOT doing this.  Do I need to go back and fix them?
0
 
LVL 32

Accepted Solution

by:
jadedata earned 250 total points
ID: 9832796
Having "undestroyed" data access objects (Access recordsets/databases) in memory has been alledged to cause Access to remain in memory even after a quit.  It has also been blamed for "insufficient memory" issues.

All I know is that my teacher told me to do it,... it doesn't cost me anything...so I do it.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9832808
I would recommend that you correct this "when you are in the neighborhood".  I wouldn't make a huge effort to try and find them all unless you are experiencing memory issues or Access remains in the task manager list after application.quit
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9833078
thanx!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question