 | |
#1
 
| |||
| |||
|
CreateQueryDef -
09-28-2010
, 01:55 PM
#2
| |||
| |||
|
|
Create a temporary query in a function using Set qryDef = currentdb.CreateQueryDef("", SqlString) on a Jet BE. Do I need to close qryDef or is setting it to Nothing enough. |
method. I'd have to look at the documentaion to be sure, but I don't
believe there even is a Close method defined for a Querydef object.
FWIW, it's not even necessary to set it to Nothing. It will get
discarded when it goes out of scope.
--
HTH,
Bob Barrows
#3
| |||
| |||
|
|
paii, Ron wrote: Create a temporary query in a function using Set qryDef = currentdb.CreateQueryDef("", SqlString) on a Jet BE. Do I need to close qryDef or is setting it to Nothing enough. If you don't call an Open method, there is not need to call a Close method. I'd have to look at the documentaion to be sure, but I don't believe there even is a Close method defined for a Querydef object. FWIW, it's not even necessary to set it to Nothing. It will get discarded when it goes out of scope. -- HTH, Bob Barrows |
Examples in Access help do not use it, or set the object variable to
Nothing. I only saw one example when Googling CreateQueryDef.
#4
| |||
| |||
|
|
paii, Ron wrote: Create a temporary query in a function using Set qryDef = currentdb.CreateQueryDef("", SqlString) on a Jet BE. Do I need to close qryDef or is setting it to Nothing enough. If you don't call an Open method, there is not need to call a Close method. I'd have to look at the documentaion to be sure, but I don't believe there even is a Close method defined for a Querydef object. FWIW, it's not even necessary to set it to Nothing. It will get discarded when it goes out of scope. |
Clean up what you create and never depend on things going out of
scope.
Also note that it has been reported that creating unnamed temp
QueryDefs does bloat your database (not much, though).
Frankly, I don't really see the utility in creating QueryDefs in
code, whether temporary or persistent. It's hardly ever the only way
to accomplish a task, and very often less efficient than other
approaches.
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
#5
| |||
| |||
|
|
Yes there is a Close method, and it does not cause an error when used. Examples in Access help do not use it, or set the object variable to Nothing. I only saw one example when Googling CreateQueryDef. |
cleanup because it's written on the assumption that VBA will
properly release all memory when variables go out of scope. As we
all know, that assumption is not 100% reliable, so we don't follow
the practices in this regard found in the help file's code examples.
By the way, the code examples in the help file are mostly quite good
and reliable. It's only in this one area that I find them lacking.
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
#6
| |||
| |||
|
|
Create a temporary query in a function using Set qryDef = currentdb.CreateQueryDef("", SqlString) on a Jet BE. Do I need to close qryDef or is setting it to Nothing enough. |
since about 1997 or so. I also don't have any action queries or
queries with parameters. All my action queries are created in VBA
code. All my parameters are also specified in code.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
#7
| |||
| |||
|
|
On Tue, 28 Sep 2010 13:55:31 -0500, "paii, Ron" <none (AT) no (DOT) com> wrote: Create a temporary query in a function using Set qryDef = currentdb.CreateQueryDef("", SqlString) on a Jet BE. Do I need to close qryDef or is setting it to Nothing enough. I'm with David. I haven't created a querydef, temp or otherwise, since about 1997 or so. I also don't have any action queries or queries with parameters. All my action queries are created in VBA code. All my parameters are also specified in code. |
quickly fell in love with never having to type """", as well as never
having to worry about delimiters anymore.
#8
| |||
| |||
|
|
Tony Toews wrote: On Tue, 28 Sep 2010 13:55:31 -0500, "paii, Ron" <none (AT) no (DOT) com wrote: Create a temporary query in a function using Set qryDef = currentdb.CreateQueryDef("", SqlString) on a Jet BE. Do I need to close qryDef or is setting it to Nothing enough. I'm with David. I haven't created a querydef, temp or otherwise, since about 1997 or so. I also don't have any action queries or queries with parameters. All my action queries are created in VBA code. All my parameters are also specified in code. Well, same here before I discovered the advantages of parameters. I very quickly fell in love with never having to type """", as well as never having to worry about delimiters anymore. |
vastly outweigh the small advantage of having to worry about
delimiters. I never type """" -- I have a constant storing that and
use that in my code, which keeps it clean and easy to understand.
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
#9
| |||
| |||
|
|
Create a temporary query in a function using Set qryDef = currentdb.CreateQueryDef("", SqlString) on a Jet BE. Do I need to close qryDef or is setting it to Nothing enough. I'm with David. I haven't created a querydef, temp or otherwise, since about 1997 or so. I also don't have any action queries or queries with parameters. All my action queries are created in VBA code. All my parameters are also specified in code. Well, same here before I discovered the advantages of parameters. I very quickly fell in love with never having to type """", as well as never having to worry about delimiters anymore. |
But when you had to come back to a query later it was a pain to figure
out what the appropriate parameter values were. Just run the select
query, manipulate it a bit and you're done.
Note though that I surround string values with ' (single quote). Then
you don't have to worry about double, triple or quadruple double
quotes.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
#10
| |||
| |||
|
|
On Fri, 1 Oct 2010 02:11:47 -0400, "Bob Barrows" reb01501 (AT) NOSPAMyahoo (DOT) com> wrote: Create a temporary query in a function using Set qryDef = currentdb.CreateQueryDef("", SqlString) on a Jet BE. Do I need to close qryDef or is setting it to Nothing enough. I'm with David. I haven't created a querydef, temp or otherwise, since about 1997 or so. I also don't have any action queries or queries with parameters. All my action queries are created in VBA code. All my parameters are also specified in code. Well, same here before I discovered the advantages of parameters. I very quickly fell in love with never having to type """", as well as never having to worry about delimiters anymore. shrug> To each thier own. But when you had to come back to a query later it was a pain to figure out what the appropriate parameter values were. Just run the select query, manipulate it a bit and you're done. Note though that I surround string values with ' (single quote). Then you don't have to worry about double, triple or quadruple double quotes. But then you have to worry about escaping apostrophes in the passed strings |
> Tony
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.