[AccessD] Excel Data Dump

[Stuart McLachlan]

Instead of trying to transfer a query named  
"qryProperty_Tenant_Data_Dump", 
you are trying to transfer a table or query named:
"SELECT tblProperty.*, tblTenant.* FROM tblProperty LEFT JOIN
 tblTenant ON tblProperty.fldPropertyID = tblTenant.fldPropertyID ORDER
 BY tblTenant.fldPropertyID, tblTenant.fldTenantSuiteNumber;*"

Try:
strsql = "qryProperty_Tenant_Data_Dump", 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,STRsql
OR
DoCmd.TransferSpreadsheet acExport, 
acSpreadsheetTypeExcel9,"qryProperty_Tenant_Data_Dump", 

On 30 Mar 2022 at 18:40, Rocky Smolin wrote:

> 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*