[AccessD] Excel Data Dump

[Rocky Smolin]

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
>