Immediately setting a variable to nothing

Question

I used a variation of this code that I found on this site, and it works well. My question is, I can't understand the rationale behind setting qdf to nothing immediately after defining it. Clearly it doesn't disrupt the process, everything works fine, but to my untrained eye, it shouldn't. Why is that in the code, and why does it not empty the variable's SQL code and export nothing to excel?

Sub Export()
    Dim cdb As DAO.Database, qdf As DAO.QueryDef
    Set cdb = CurrentDb

    Const xlsxPath = "redacted 1"

    ' create .xlsx file if it doesn't already exist, and add the first worksheet

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Ledger Data by Channel", xlsxPath, True


    'file exists now, so this will add a second worksheet to the file
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Premium Reserves", xlsxPath, True

    Set qdf = cdb.CreateQueryDef("Reserve-from Wakely", _
            "SELECT * FROM [redacted 4] UNION SELECT * FROM [PAI ALR] UNION SELECT * FROM [Prior Month PAI DAC]")
    Set qdf = Nothing
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "redacted 2", xlsxPath, True
    DoCmd.DeleteObject acQuery, "Reserve-from Wakely"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "redacted 3", xlsxPath, True

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Claims Data", xlsxPath, True

    Set cdb = Nothing
End Sub

A QueryDef is created then destroyed but it is never referenced again; it is not part of the working code. The three occurrences (declaration, setting, setting to nothing) could be deleted and nothing would change. It may have been one avenue that the coder was exploring and gave up on in favor of `DoCmd.TransferSpreadsheet ...`. — , Sep 16 '15 at 20:18
... and for a general "Do I have to set things to `Nothing`" question, see [When should an Excel VBA variable be killed or set to Nothing?](http://stackoverflow.com/q/19038350/11683). — GSerg, Sep 16 '15 at 20:19

score 3 · Accepted Answer · answered Sep 16 '15 at 20:22

It is because the QueryDef is not only contained in the qdf variable, but it is also stored in QueryDefs collection and saved to disk...

As explained here: https://msdn.microsoft.com/en-us/library/office/ff845129(v=office.14).aspx

To create a new QueryDef object, use the CreateQueryDef method. In a Microsoft Access workspace, if you supply a string for the name argument or if you explicitly set the Name property of the new QueryDef object to a non–zero-length string, you will create a permanent QueryDef that will automatically be appended to the QueryDefs collection and saved to disk

Still this is a confusing coding practice. They should have put it in a statement form: `cdb.CreateQueryDef "Reserve-from Wakely", "SELECT *"`. — GSerg, Sep 17 '15 at 08:30

Immediately setting a variable to nothing

1 Answers1