[AccessD] Excel Data Dump

[Rocky Smolin]

Dear List:

My client wants an excel data dump of all the fields in all of the
commercial rental properties he owns and manages as well as all the fields
of all the tenant records in that property. So on his Property Form I have
a button labeled Excel Data Dump.

So I create a query like so and use it to export the data to a spreadsheet:

strSQL = "SELECT tblProperty.*, tblTenant.* FROM tblProperty LEFT JOIN
tblTenant ON tblProperty.fldPropertyID = tblTenant.fldPropertyID"

     strSQL = strSQL & " ORDER BY tblTenant.fldPropertyID,
tblTenant.fldTenantSuiteNumber;"
    strDT = Format(Now, "MM-DD-YY HH_MM_SS AM/PM") & ".xls"
    strFileName = gstrFrontEndPath & "Property_Tenant_Data_Dump_" & strDT

    On Error Resume Next
    DoCmd.DeleteObject acQuery, "qryProperty_Tenant_Data_Dump"
    On Error GoTo 0

    CurrentDb.CreateQueryDef "qryProperty_Tenant_Data_Dump", strSQL
    CurrentDb.QueryDefs.Refresh
    Me.txtSQL = strSQL
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strSQL,
strFileName, True

I can copy the text in the  text box txtSQL and paste it into the query
view of a new query.  It works perfectly in a query show all property
fields and all tenant fields

But when it gets to the line DoCmd.TransferSpreadsheet I get the error:

Error 7871 the table name you entered doesn't follow the the object naming
rules

In my test case Me.txtSQL has the following:

*SELECT tblProperty.*, tblTenant.* FROM tblProperty LEFT JOIN tblTenant ON
tblProperty.fldPropertyID = tblTenant.fldPropertyID ORDER BY
tblTenant.fldPropertyID, tblTenant.fldTenantSuiteNumber;*

*Seems pretty simple. I've tried twelve ways from Sunday to figure out
exactly what is causing the error but no cigar.  *

*Any ideas?*


*MTIA*

*Rocky*