asked on

How to release object when I "Call getQuerydefFunction"

I try to release variables after I use them.

So, I coded something as follows (this is greatly simplified)

Code #1
sub ChangeQueryDef(qname as string, sql as string)
dim getChangeQueryDef as querydef
set getChangeQueryDef = currentdb.querydefs("myname")
.... getChangeQueryDef = sqlname
set getChangeQueryDef = nothing
end sub

But, I want to change the subroutine to so that it can sometimes be called as a function.

Code #2
sub getChangeQueryDef(qname as string, sql as string) as querydef
set getChangeQueryDef = currentdb.querydefs("myname")
.... getChangeQueryDef = sqlname
set getChangeQueryDef = nothing
end sub

So, here is how I could use the routine and be sure the querydef variable gets de-allocated.

Code #3
set qdf = getChangeQueryDef("qGeneric", "select * from table")

... do stuff with qdf

set qdf = nothing

But, what happense if I simnply call the routine? My theory is the the variable does not need to be de-allocated.
But, I know that Access and Excel both sometimes create hidden variables that don't get deallocated until the application is closed.

I wonder if the following code might have that problem

Code #4
call getChangeQueryDef("qGeneric", "select * from table")

ASKER CERTIFIED SOLUTION

ioane

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Berke

ASKER

"Once the function ends, the variables are automatically removed."

Yes, that is true WHEN YOU DEFINE A VARIABLE.

But, in the attached code, sub Test1 does not define a variable.
I have discovered in Excel sometimes generates its own internal variables that don't get deallocated.
For instance, I once discovered that vba creating new instances of Excel were still showing in task manager even after I closed my Excel program. I traced it to a code that said "debug.print cells.address". Excel noticed that I did not supply an Application object. Excel generated a module level variable, and used it. When my subroutine exited, that variable was not deallocated causing the excle instance to hang.

I got to run now, but I will give more details tomorrow.

sub test1()
call  getChangeQueryDef("qGeneric", "select * from table")
end sub

Public Function getChangeQueryDef(qname as string, sql as string) as QueryDef
    set getChangeQueryDef = currentdb.querydefs(qname )

End Function

Open in new window

Robert Berke

ASKER

I am afraid I did not state this question clearly, and I will not have time to clean it up for at least a week, maybe more.

So, I am awarding points mainly to reward effort from the experts that commented.

I will eventually resubmit a clarified question. At that time I will crosslink a comment here.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

ok ... thx

ioane

Cheers.

How to release object when I &quot;Call getQuerydefFunction&quot;

How to release object when I "Call getQuerydefFunction"