CODEWITHSUNDEEP
vba
DemonLlama
DemonLlama

Reputation: 47

Immediately setting a variable to nothing

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

Upvotes: 1

Views: 395

Answers (1)

Philippe
Philippe

Reputation: 558

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

Upvotes: 3

Related Questions