Clearing memory

[redBo]

Hello Guys/Gals

I am a novice to VBA+Access. I am not sure how access/vba handles memory allocation. For example, if I declare variables inside objects (ie forms), are they cleared as soon as the objects are closed? Or do I have to clear this manually, and if so , how(what is the code) would I clear these.

I ask this I have declared arrays and Recordset's in forms, and want to minimise memory use as a good practise (mind you, I am likely to commit million other sins; but hey, I am not a software engineer.

 

[Moniker]

You're talking about variable scope. For a form, the arrays/variables/other assignments exist as long as the form is open. Once the form is closed, the memory used by such constructs is released.

Things that are not cleared are global constants/variables. These are cleared once the DB is closed and are not form/report dependent.

For functions/subroutines, the variable declarations only exist within their specific function/subroutine and nowhere else. For example, if you have a subroutine that gets a filename and stores it in txtFileName, that variable only exists in that subroutine. If you want it to be available in other subroutines/functions, then you have to "pass it" as it were as a parameter or declare it as a global variable (or store it in a table).

 

[boblarson]

Normally, most variables (except like Moniker mentions as well) you declare don't need to be set to nothing to clear them from memory. However, there are variables that deal with objects that you will need to do garbage collection on as they won't automatically be destroyed upon Access closing.

An example would be if you use code to CreateObject, or creating an ADO recordset.

To clear variables from memory, you set them to nothing.

So, if you had an ADO Recordset object, you would first close it

rst.Close

and then set it to nothing

Set rst = Nothing

 

[Moniker]

Oops. Thanks for the catch Bob. Object variables should be set to nothing like that.

Basically, anytime you use a CreateObject(), GetObject(), or a Set statement, whatever you're creating, getting, or setting should be set to Nothing when you're done with it, as Bob illustrated. (Note that "rst" in his example was a variable name and not a keyword.)

 

[monbois]

Re: Clearing memory - Declaring Global Variables

You need to declare your variables globally instead of locally. Instead of declaring:

Dim strText as String

in a form. You want to declare:

Global strText as String

in a module. That way you can use the variable in any form or report (or query if you link it to a function) and the last applied value to the variable will (usually) stick until the database is closed.

This also saves the time and trouble of creating the same variables in multiple forms.

 

[boblarson]

Re: Clearing memory - Declaring Global Variables

You need to declare your variables globally instead of locally. Instead of declaring:

Dim strText as String

in a form. You want to declare:

Global strText as String

in a module. That way you can use the variable in any form or report (or query if you link it to a function) and the last applied value to the variable will (usually) stick until the database is closed.

This also saves the time and trouble of creating the same variables in multiple forms.

Global variables should be used only when necessary. Declaring variables in your procedures if they do not need to be used elsewhere is much better, not only for memory management, but for the sheer fact that global variables can lose their values if an unhandled error occurs. This can cause problems (of course having robust error handling is obviously good to have).

Also, the use of the keyword GLOBAL is actually no longer the preferred method in VBA. The current preferred method is

PUBLIC mstrMyString As String

to declare a global variable.

 

[monbois]

I use only global variables so I can call them whenever and whenever I want. I rarely need a single variable for a single use in a single form or report. And what about queries? You can't filter a query with a variable value unless it's declared globally and then transferred to a function.

I've developed dozens of Access databases for Fortune 500 companies for 15 years. I always declare all my variables globally in a Procedures module. I've rarely had issues with a variable losing its value, but global variables aren't any more suseptible to this than local ones.

Think outside the box.

I'll test declaring global variables with Public instead of Global, but I suspect this is just a fad. What I can't understand is why you would poo-poo any global variables and then have the gall to dictate how to declare something you claim shouldn't be used in the first place?

 

[boblarson]

I use only global variables so I can call them whenever and whenever I want.

So, the concept of using the appropriate tool for the appropriate application doesn't compute? It is like always having a winter parka on because it does the job of keeping you warm, even though there are times where a lighter jacket would do the job nicely and not be overkill.

And what about queries? You can't filter a query with a variable value unless it's declared globally and then transferred to a function.

Incorrect answer. You most certainly can use a variable from inside a module or procedure in a function to use inside a query even though it is not declared globally.

I've developed dozens of Access databases for Fortune 500 companies for 15 years.

Happy to hear. So have I. But we all learn something new at times. I learn new things all of the time here and elsewhere.

I always declare all my variables globally in a Procedures module. I've rarely had issues with a variable losing its value, but global variables aren't any more suseptible to this than local ones.

Scope, I'm surprised you seem unaware about scope. Local variables have a specific scope, and again, it is like the parka example I gave.

Think outside the box.

There is thinking outside the box and then there is thinking which is just out there. I'm afraid yours appears to fall into the latter category. But I'll let some of our other experts here join into the fray and see what they think.

I'll test declaring global variables with Public instead of Global, but I suspect this is just a fad.

I had the Microsoft Access Development Team tell me this specifically when either at one of the MVP Summits or at one of the Development Kitchens one of which I attended.

What I can't understand is why you would poo-poo any global variables and then have the gall to dictate how to declare something you claim shouldn't be used in the first place?

Never said they shouldn't be used. I said, there is a time and a place for everything and there is a time and a place for global/public variables. It just is not all of the time.

 

[spikepl]

I just couldn't let this pass, so here goes. At some stage I spent a couple of years reworking code comitted by people who "thought outside the box". They were convinced that they did not need to follow any conventions or working practices. This cost my employer hundreds of thousands.

There are good reasons for limiting the scope of variables to the minimum necessary. It makes it easier to follow the program flow. Maintaining someone elses code is bad enough. Even maintaining one's own code can be a pain, especially if one did some "smart" things that are incomprehensible 6 months later. If no one else ever touches your code, then you can of course code whichever way you wish.

 

[ChrisO]

Monbois, this is my take on the subject…

>>I always declare all my variables globally in a Procedures module.<<
Fascinating stuff; may I ask what a Procedures module is?

>>Think outside the box.<<
I hope I’m not repeating an urban myth here but it is a good story anyway:-
Bill Gates, who was (is?) a very good programmer, was asked how he kept thinking outside the box.
His reply was…what box?

Your first reply on this site was to a thread which was eight years old.
Your first reply in this thread is in a thread which was six years old.

My impression, after only your third post, is that you write more that you read and talk more than you listen.
It’s a one-way conversation you are having with yourself.

Chris.

 

[Uncle Gizmo]

The issue of global variables raises its head from time to time and I have tried to find out from various experts why you can't/shouldn't use global variables.

I did get what I considered a good answer back from someone who that someone was or when I got the answer I'd do not remember but the advice and knowledge has somehow stuck with me. Whether the advice and knowledge is good and well founded I cannot be 100% sure but it does make a lot of sense to me.

Basically there's no reason why you shouldn't use a global variable, however there are many alternatives which are much better, safer, tidier, and a more professional looking.

You can use an enumeration, you can use a constant, you can use a function or more correctly a subroutine, in other words there's no good reason for using a global variable except that it's quick and easy, it does expose you to more risk so you would be more prudent using one of the alternatives mentioned.

 

[daievans]

Well - that seems to have cleared "my wood" from the Access forest ....
Done with such gentility, to boot.

 

[ChrisO]

To my way of thinking, this is the essence of Uncle Gizmo’s post:-
>>in other words there's no good reason for using a global variable except that it's quick and easy<<
I think we could also add to that >>except if there is no other way<<

This is how I see the problem: quantity on the www reigns supreme over quality.

This is how I see it getting started…
Someone, particularly if they are seen as some guru, makes a statement. The flock reads it, agrees with it (if they didn’t agree with it they wouldn’t be part of the flock) and reposts it.
The post count increases.
The next person who comes along reads the repost and reposts that.
The snowball grows.
Very soon the snowball is almost irresistible.
If so many people are saying something, it must be correct; right?

Wrong! The problem was that people believed the guru but did not question the guru.
Why? Well, we do not question a guru when so many people believe in the guru.

Case in point, which most of us would have seen:-
“Global variables can lose their value when an unhandled error occurs.”
Wrong!
Global variables do not lose their value even if an unhandled error occurs.
After the error, we can go into debug mode and see the value of global variables.

Furthermore, if global variables were lost on unhandled errors then the global Err Object would lose things like Err.Number and Err.Description. So that logic should imply that, after an error, all Err.Numbers are zero and all Err.Descriptions are zero length strings. One would think that people should see for themselves that such an outcome would be illogical.

And, yet, such things propagate on the www.
There are plenty more on the www and it is not restricted to Global variables.

The real problem is that quantity becomes the guru and most people do not question quantity.

Chris.

 

[The_Doc_Man]

ChrisO - you disagreed that global variables will lose their values when an unhandled exception occurs. I have to at least partly disagree with you.

Global variables declared via the PUBLIC declaration (vice DIM or GLOBAL) in the declaration area of a general module can, indeed, lose their values after some exceptions. It has happened to me when I made the mistake of not trapping quite everything I should have trapped. You may deny it, but it was real. I think you've seen my posts enough to know that I'm not totally incompetent at picking through the rubble to analyze what has gone wrong. After all, I have so much experience with things going wrong, working with the government like I do...

I will state this: There is enough damage done to your programming context after an unhandled exception that you might as well decide that you've lost everything. Which is why you apply Murphy's law to assume that whatever could go wrong WILL go wrong.

Thus, when writing important code, assure that you DO have a way to trap exceptions and keep them from reaching the Access "Last Chance" handler. It is that "Last Chance" handler that loses your context for you if you didn't take extreme steps to preserve your environment.

As to when you would and when you would not use global/public variables? There are a million reasons to use globals and a million more to not use them. I guess it will always boil down to providing the right tool for the right job. Why do you suppose pro golfers are allowed 14 clubs in the bag instead of just four clubs?

The danger of using globals/publics is that they lead to "side effects" if you aren't careful. A "side effect" such as having a subroutine change a global variable that was neither an input parameter nor an output parameter to that routine. Therefore, the change would be undetectable except if you were debugging with a WATCH having been set. I.e. major confusion factor.

 

[ChrisO]

Doc.

In a computer, things happen for a reason, they don’t just happen.

Global variables, and that includes Global Objects, don’t just lose their value they get reset. The reset is deliberate and the variables are reset to a particular value.
Numbers, including dates, to 0.
Strings to a zero length string.
Variants to Empty.
Objects to Nothing, and if the Object is, say, a recordset, then the recordset is closed.

Take the simple case of a Long. It might have a value of 123456. If it simply lost its value then the value might become 8888, but it doesn’t, it becomes 0. A string of “Sam” becomes “” not “Fred”. Can this simply be by chance? Of course not.

Global variables do not simply lose their value, their value is Reset. That Reset is deliberate and it is done to initialise the variables back to some known starting value. The program started that way and it should restart that way.

The Reset can happen after coming out of debug mode. It will happen if we use Reset on the menu. It will happen if we execute a DoCmd.Quit or Application.Quit instruction. Incidentally, those last two happen while code is still running and very few people, even up to guru-ship level, know about it.

I’m not just being pedantic here. Variables are Reset under program control, they are not simply lost. To think that they might simply lose their value is just ‘fairies at the bottom of the garden’ stuff.

How far would a computer program get if it ran on ‘fairies at the bottom of the garden’? If we go down that track then all we are doing is inflating the snowball with gobbledygook.

If it did happen in a computer then it should be reproducible.
But, can anybody reproduce it?

Chris.

 

[Galaxiom]

Global variables declared via the PUBLIC declaration (vice DIM or GLOBAL) in the declaration area of a general module can, indeed, lose their values after some exceptions. It has happened to me when I made the mistake of not trapping quite everything I should have trapped. You may deny it, but it was real.

I agree with Chris. There is nothing that is going to reset a variable when the code execution breaks. By definition it has stopped executing at that point.


We all carry superstitions from our early experiences when we didn't have enough knowledge to fully understand what was happening, be that in life or programming.

 

[gemma-the-husky]

with regard to this

i was recently iterating all forms in a recordset

dim obj as object
dim frm as form
 
for each obj in currentproject.allforms[COLOR=red] 'yes, it does seem to be an object, not a form at this stage.[/COLOR]
    docmd.openform obj.name acdesign
    set frm = forms(obj.name)
    docmd.close acform frm.name
   [COLOR=red]set frm = nothing[/COLOR]
[COLOR=black]doevents[/COLOR]
next

if I don't release the memory with the code in red, eventually i get out of memory issues.


------

I think the discussion about what happens when a program crashes is a bit moot. public variables certainly "lose" the value - or get reset, however you want to term it. An instance of a class will also lose it's value (or get set to nothing)

I presume it's to do with garbage collection on the stack.

i assume the new concept of tempvars, and the err object are not stored on the stack along with public variables, so they do get preserved, as part of the program state.

I too use a lot of public variables - although I am trying to use other techniques to manage them.

I tihnk the main thing is to avoid unhandled errors.

Maybe some of us are just able to express results with more academic rigour, while for others it's more informally based on empirical results.

 

[ChrisO]

One step at a time:-

Public A As Long

Sub Test()
    Dim B As Long
    
    A = 10
    
    B = A / 0

End Sub

Run the code.
Enter debug mode.
Hover over A.
A has a value of 10.

The ERROR did not cause A to lose its value.

I can not make it simpler than that.

Chris.

 

[gemma-the-husky]

yes, but you cannot get past this bit of code without either modifying the statement or Ending the program.

And Ending the program causes the the value of A to reset to zero.

 

[ChrisO]

Not exactly…

The ERROR did not cause A to lose its value, but the RESET will cause A to be reset to zero if the RESET occurs.

The resetting of the Global variable is done by the RESET not the ERROR.

Now people might say “Oh! We knew that, you’re just being pedantic.”
Well, I’ll not just being pedantic, hopefully I am being pedantic.
I work hard to try and be as pedantic as the machine I am using.

So let’s go back to the quote I made in post #13.
“Global variables can lose their value when an unhandled error occurs.”
The above is wrong but people still keep quoting it from the www and reposting it on the www.
They do that because they do not know the difference.

“Global variables will lose their value if a RESET occurs.”

An ERROR is not a RESET; they are two separate things and should not be confused.
We can have an error without a reset and we can have a reset without an error.
People should not simply lump the two together as if they are the same thing.

Chris.