[AccessD] Excel Data Dump

[Paul Hartland]

Should the last line be
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryProperty_Tenant_Data_Dump",
strFileName, True

And not
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strSQL,
strFileName, True


On Thu, 31 Mar 2022, 04:03 Rocky Smolin, <rockysmolin2 at gmail.com> wrote:

> Paul
>
> the difference between the routine that prints one property versus the one
> that prints all properties is that the one that only prints one property
> enumerates the fields:
>
> strSQL = "SELECT tblProperty.fldPropertyName AS Property,
> tblTenant.fldTenantSuiteNumber AS Suite, " _
>         & "tblTenant.fldTenantCompany AS Company, tblTenant.fldTenantRent
> AS [Rent(1)], tblTenant.fldTenantName AS Tenant, " _
>         & "tblTenant.fldTenantStartDate AS [Start Date],
> tblTenant.fldTenantEndDate AS [End Date], tblTenant.fldTenantRent AS
> [Rent(2)], " _
>         & "tblTenant.fldTenantSize AS [Size],
> tblTenant.fldTenantReminderDate AS [Reminder Date], " _
>         & "tblTenant.fldTenantSecurityDeposit AS [Security  Dep],
> tblTenant.fldTenantLease AS [Leasing] " _
>         & "FROM tblProperty LEFT JOIN tblTenant ON
> tblProperty.fldPropertyID = tblTenant.fldPropertyID"
>
> whereas the one that prints all fields uses *:
>
>     strSQL = "SELECT tblProperty.*, tblTenant.* FROM tblProperty LEFT JOIN
> tblTenant ON tblProperty.fldPropertyID = tblTenant.fldPropertyID"
>
>
> Unfortunately, there are so many fields between the two fields that I the
> sql view has more characters than is allowed in s string.
>
> r
>
> On Wed, Mar 30, 2022 at 7:29 PM Paul Hartland via AccessD <
> accessd at databaseadvisors.com> wrote:
>
> > 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
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>