Deleting a Temp QueryDef (1 Viewer)

[whdyck]

I'm using MS Access 2003.

I have a custom function that opens a recordset using a temporary pass-thru QueryDef object. Here's the guts of my code:

Public Function OpenRecordsetPassThru(ByVal pstrSQL As String, Optional ByVal pstrDatabase As String = "ORACLE", ...) As Recordset
On Error GoTo OpenRecordsetPassThruErr
 
    Dim db As Database
        Set db = CodeDb
    Dim qdTemp As DAO.QueryDef
    Dim strConnect As String
    Dim lngOdbcTimeout As Long
 
    ' Main Line
    Select Case pstrDatabase
        Case "DYNAMICS"
            strConnect = cstrDynamicsConnect
            lngOdbcTimeout = 60
        Case Else   'Oracle
            strConnect = GetOracleConnect
            lngOdbcTimeout = 3600
    End Select
 
    ' Create a temporary pass-thru query
    Set qdTemp = db.CreateQueryDef("")
 
    ' Set properties of temp pass-thru query
    qdTemp.Connect = strConnect
    qdTemp.ODBCTimeout = lngOdbcTimeout
    qdTemp.SQL = pstrSQL
    qdTemp.ReturnsRecords = True
 
    Set OpenRecordsetPassThru = qdTemp.OpenRecordset(pintType, pintOptions)
 
...

I'm just wondering whether I should explicitly delete or close the temp QueryDef object, and if so, where? If yes, I'm thinking that I'd want to do it right after

Set OpenRecordsetPassThru = qdTemp.OpenRecordset(pintType,

but can I do that? (I thought the Set statement should be the last of the function.)

I notice that in the example on the following page:
http://msdn.microsoft.com/en-us/library/office/ff845129.aspx
the temp QueryDef is not deleted or closed.

Since this function can be run many times in a given session, I'd want to ensure that the temp QueryDef objects are being cleaned up as I go.

Thanks for any help you can give.

Wayne

 

[mdlueck]

Since this function can be run many times in a given session, I'd want to ensure that the temp QueryDef objects are being cleaned up as I go.

Best name the DAO.QueryDef, and delete it by name when you are done with it. Here is sample code of how I make use of DAO.QueryDef objects:

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605#post1119605

Also, there are numerous query type things with Access leaves orphaned / abandoned in the database. I developed a process to delete all of those:

VBA to Cleanup A2007 DB Extra Objects
http://www.access-programmers.co.uk/forums/showthread.php?t=226466

Fro example, I delete (via the Access UI) the Linked Table objects prior to production deployment. Access renames those, but does not delete them. Access renames them to having a leading ~ and then random letters/numbers. Otherwise, I suppose funky useless ~ named entries would forever be stuck in the database.

And finally, documented how to prep an Access FE for use here:

NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
http://www.access-programmers.co.uk...to_Decompile_/_Compact_/_Compile_an_Access_DB