[AccessD] Excel Data Dump

[Rocky Smolin]

Paul:

I get Invalid Argument both ways.

Thanks


Rocky


On Wed, Mar 30, 2022 at 8:09 PM Paul Hartland via AccessD <
accessd at databaseadvisors.com> wrote:

> 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
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>