When to close QueryDef

[alex]

When to close QueryDef

Hello,

I have a form in which users run a variety of queries (built
dynamically based on what gets entered/clicked on the form) through a
query def with linked SQL Server tables.

The code looks like this:
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’
With dbs
Set qdf = .CreateQueryDef("", MyQuery())
qdf.ODBCTimeout = Me.TimeOut 'combo box that holds time intervals
qdf.Execute 'run query
qdf.Close
End With

Set qdf = Nothing 'clean up
Set dbs = Nothing 'clean up
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’
The user could run one query or 100 queries before closing the form;
i.e., looping through the query def 100 times! Every time the user
runs a query, it loops through the code above.

My question is: when should I close the qdf (like I have above or when
the form closes)? Also, when/if should I set qdf = nothing. Also,
had I named the query def, when should it be deleted?

I know the generic answer is after I’m finished, but I’m technically
not finished with it until the form closes. I want the query to be as
fast as possible…

Thanks,
alex

 

[Paul Shapiro]

I have a form in which users run a variety of queries (built
dynamically based on what gets entered/clicked on the form) through a
query def with linked SQL Server tables.

The code looks like this:
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’
With dbs
Set qdf = .CreateQueryDef("", MyQuery())
qdf.ODBCTimeout = Me.TimeOut 'combo box that holds time intervals
qdf.Execute 'run query
qdf.Close
End With

Set qdf = Nothing 'clean up
Set dbs = Nothing 'clean up
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’
The user could run one query or 100 queries before closing the form;
i.e., looping through the query def 100 times! Every time the user
runs a query, it loops through the code above.

My question is: when should I close the qdf (like I have above or when
the form closes)? Also, when/if should I set qdf = nothing. Also,
had I named the query def, when should it be deleted?

I know the generic answer is after I’m finished, but I’m technically
not finished with it until the form closes. I want the query to be as
fast as possible…

I don't know if it's still true, but it used to be that failing to close a
DAO object potentially reduced application stability. I make it a practice
to close objects in the routine that opened them, so your code looks fine to
me. Is performance a problem? If not, there's no impact of the minor time to
create and destroy the object multiple times. If performance is a problem,
you could create the querydef as a module-wide variable in the form's OnLoad
event and destroy it in the OnUnload event.

 

[alex]

I don't know if it's still true, but it used to be that failing to close a
DAO object potentially reduced application stability. I make it a practice
to close objects in the routine that opened them, so your code looks fineto
me. Is performance a problem? If not, there's no impact of the minor timeto
create and destroy the object multiple times. If performance is a problem,
you could create the querydef as a module-wide variable in the form's OnLoad
event and destroy it in the OnUnload event.- Hide quoted text -

- Show quoted text -

Thanks Paul for responding...
The query def works very well actually. I don't really understand
exactly what qdf.close or set qdf = nothing does? I'm assuming it
closes the variable, but the query def is temporary anyway. Can I
pass different sql strings to a query def that remains open? Much
like the example at the end of your response string...
alex

 

[Paul Shapiro]

Thanks Paul for responding...
The query def works very well actually. I don't really understand
exactly what qdf.close or set qdf = nothing does? I'm assuming it
closes the variable, but the query def is temporary anyway. Can I
pass different sql strings to a query def that remains open? Much
like the example at the end of your response string...
alex

I think you can pass a new sql statement to a querydef and then re-execute
it, but I haven't used it that way so you should try it to see. I've used a
parameterized querydef that remains open by assigning new parameter values
and then re-executing the query.

Closing the querydef releases the data. Setting the querydef variable to
nothing releases the entire querydef object. This is the part that you want
to make sure happens when you're really finished.

The other thing I would suggest is testing the code both a) keeping a single
querydef object open, and b) discarding it every time it's used and
re-creating a new one for the next operation. If there's no performance
issue with discarding it every time, that seems simpler and more reliable to
me.

 

[alex]

I think you can pass a new sql statement to a querydef and then re-execute
it, but I haven't used it that way so you should try it to see. I've useda
parameterized querydef that remains open by assigning new parameter values
and then re-executing the query.

Closing the querydef releases the data. Setting the querydef variable to
nothing releases the entire querydef object. This is the part that you want
to make sure happens when you're really finished.

The other thing I would suggest is testing the code both a) keeping a single
querydef object open, and b) discarding it every time it's used and
re-creating a new one for the next operation. If there's no performance
issue with discarding it every time, that seems simpler and more reliableto
me.- Hide quoted text -

- Show quoted text -

Thanks Paul for your help again!

What you said makes sense…one thing that confuses me, however:

Take this code snippet from Ken Snell (which works just fine) for
exporting to Excel:
'''''''''''''''''''''''''''''''''''''''''''''''''''''
strQDF = "_TempQuery_"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF,"C:\MyFolderName\MyFileName.xls"
dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing
'''''''''''''''''''''''''''''''''''''''''''''''''''
Ken assigns the querydef name to a variable;
Sets the querydef;
Closes the querydef;
And then sets it to nothing…

After closing it and setting it to nothing, it’s then referenced in
the docmd method. You would think that there would be nothing to
reference, but that’s not the case because the TransferSpreadsheet
method works!
~alex

 

[Bob Quintal]

m:

Thanks Paul for your help again!

What you said makes sense…one thing that confuses me, however:

Take this code snippet from Ken Snell (which works just fine) for
exporting to Excel:
'''''''''''''''''''''''''''''''''''''''''''''''''''''
strQDF = "_TempQuery_"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF,"C:\MyFolderName\MyFileName.xls"
dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing
'''''''''''''''''''''''''''''''''''''''''''''''''''
Ken assigns the querydef name to a variable;
Sets the querydef;
Closes the querydef;
And then sets it to nothing…

After closing it and setting it to nothing, it’s then referenced
in the docmd method. You would think that there would be nothing
to reference, but that’s not the case because the
TransferSpreadsheet method works!
~alex

The CreateQueryDef method does not save an unnamed query to disk.
Giving it a name causes the CreateQuerydef method to automatically
save the query to disk.

Ken's code does that because the TransferSpreadsheet must read a
table or query from disk, and opens the query from that saved
definition.

So Ken is not referencing the querydef which has been closed, he
passes the name of the saved query to the TransferSpreadsheet method,
which reads it from disk.

 

[alex]

m:











The CreateQueryDef method does not save an unnamed query to disk.
Giving it a name causes the CreateQuerydef method to automatically
save the query to disk.

Ken's code does that because the TransferSpreadsheet must read a
table or query from disk, and opens the query from that saved
definition.

So Ken is not referencing the querydef which has been closed, he
passes the name of the saved query to the TransferSpreadsheet method,
which reads it from disk.

--
Bob Quintal

PA is y I've altered my email address.- Hide quoted text -

- Show quoted text -

Thanks Bob; that helps my edification.
So closing the qdf and setting it to nothing basically releases the
data (contained in the def) from the module in which it was created.
alex