[AccessD] Excel Data Dump

[Paul Hartland]

Hi Rocky,

Just an idea what does strfilename produce, you have the datetime format to
show am/pm yet the hours are 24 hour when using capital HH so not really
required anyway, but apart from that when the debug passes strfikename have
you looked at it ?

strDT = Format(Now, "MM-DD-YY HH_MM_SS AM/PM") & ".xls"
    strFileName = gstrFrontEndPath & "Property_Tenant_Data_Dump_" & strDT

Paul

On Thu, 31 Mar 2022, 02:40 Rocky Smolin, <rockysmolin2 at gmail.com> 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*
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>