empty strings on routine exit (1 Viewer)

[irish634]

When constructing strings in vba (SQL statements, MsgBox prompts, etc) is it necessary to clear the string when exiting the function/sub?

Meaning is it necessary to do something like this at the end of the code:

     sSQL = "" or sSQL = vbNullString
     sPrompt = "" or sSQL = vbNullString

 

[marianne]

may I know why?

 

[ajetrumpet]

When constructing strings in vba (SQL statements, MsgBox prompts, etc) is it necessary to clear the string when exiting the function/sub?

Meaning is it necessary to do something like this at the end of the code:

     sSQL = "" or sSQL = vbNullString
     sPrompt = "" or sSQL = vbNullString

Not if the variables are declared inside the procedure. If they are declared as PUBLIC though, it is a good idea to clear them when not needed, to conserve memory space.

 

[marianne]

yep. conservation of memory.

 

[DCrake]

You can also use

Set memvar = nothing

This actually releases the declaration from member, not just the contents of the memvar.

David

 

[irish634]

Not if the variables are declared inside the procedure. If they are declared as PUBLIC though, it is a good idea to clear them when not needed, to conserve memory space.

What if they are not declared "PUBLIC" but still declared outside the procedure?

IE:

Option Compare Database     
Option Explicit

Dim sSQL as String
Dim sPrompt as String

Function Whatever().....

In my scenario, I have 3 procedures in a module, two of which use the above variables.

DCrake: So if I put "Set memvar = nothing" at the end it will release everything (strings, declarations, etc) in the procedure?

 

[DCrake]

When you Dim a variable such as

Dim tmp As String

You are doing 2 things

first you are declaring that you want to use a temporary container called tmp to store something in it.

secondly by stating is is going to be a string Access can anticipate what type of expression to expect.

When you actually use this memvar (tmp) such as

tmp = "Hello world" you are stuffing the contents into it.

Your question about clearing the contents by using tmp = "" is fine but the variable tmp still exists and Access needs to rember this. If it is a public/global variable. If however you do not need this anymore you can use the Set tmp = nothing to destroy the instance of tmp.

It's a bit like a bucket of sand first you buy a bucket then you put sand in it. If you then pour out the sand it becomes lighter and easier to carry. But you still have the bucket to carry about with you. If you decide you do not want it any more and throw the bucket away you have further reduced your burden. Anyway you can always buy a new one should youi want one.

David

 

[irish634]

When you Dim a variable such as

Dim tmp As String

You are doing 2 things

first you are declaring that you want to use a temporary container called tmp to store something in it.

secondly by stating is is going to be a string Access can anticipate what type of expression to expect.

When you actually use this memvar (tmp) such as

tmp = "Hello world" you are stuffing the contents into it.

Your question about clearing the contents by using tmp = "" is fine but the variable tmp still exists and Access needs to rember this. If it is a public/global variable. If however you do not need this anymore you can use the Set tmp = nothing to destroy the instance of tmp.

It's a bit like a bucket of sand first you buy a bucket then you put sand in it. If you then pour out the sand it becomes lighter and easier to carry. But you still have the bucket to carry about with you. If you decide you do not want it any more and throw the bucket away you have further reduced your burden. Anyway you can always buy a new one should youi want one.

David

Thank you for that explanation and analogy. It makes perfect sense. I've always seen it done both ways where the string is released (tmp = "") and others where it is not. I've only ever released when I use objects such as DBS and RST. I've only ever cleared a string when I found I caused an issue such as appending to the string by mistake. So I suppose releasing all the variables would be a good habit to pick up.

One other question:
In one of my procedures I typed: "Set sSQL = Nothing" instead of "sSQL = "" " When I compile I get a "Compile Error - Object Required"

I am using Access 2000 here at work. Is this not a valid statement for Access 2000? What versions would I use it in?

Thank you,
Craig

 

[ajetrumpet]

In one of my procedures I typed: "Set sSQL = Nothing" instead of "sSQL = "" " When I compile I get a "Compile Error - Object Required"

I don't know this for sure, but I don't believe you can set strings to equal NOTHING. I believe that is only for database objects, like forms, reports, recordsets, querydefs, etc...

I could be wrong, but that sounds right. I have always set my strings equal to "" when I empty them. Either that, or I set them to =EMPTY. I also use EMPTY for booleans when I clear them.

 

[SOS]

If you instantiate an object then you can set it = nothing. If you don't instantiate (and most variables are that type) then you don't really need to do anything as they are destroyed upon the end of their lifecycle. It is only the instantiated objects you need be concerned with doing garbage collection with. If you instantiate a bunch of Excel applications then they will exist and continue to exist even if you close Access. That is why you would need to destroy them so they don't become a memory leak. But normal variables like sSQL and dteMyDate, etc. do not need to be set to "" or set to nothing. Just let the program manage them in their lifecycle. There are no memory leaks with those.

 

[gemma-the-husky]

sos is surely correct

temporary variables are created/pushed on to the stack, and destroyed when the procedure in which they have scope is closed

this is why recusrion to too deep a level can crash a program by overfilling the stack with temporary variables - you get a stack full error

-------
i didnt realise the possibilty with the instantiated variables, which does make sense also.

does this apply to internal things like set rst = openrecordset etc

 

[SOS]

does this apply to internal things like set rst = openrecordset etc

It is "possible" but unlikely. It is more likely to happen with out of process variables (objects instantiated that exist outside of Access).