[AccessD] Excel Data Dump

[Rocky Smolin]

Paul:

No problem. (I know access - so many times - whatever it is - that's not
it.) I simplified the routine to

Private Sub cmdExcelDataDump_Click()


    Dim strFileName As String

    strFileName = gstrFrontEndPath & "Property_Tenant_Data_Dump" '_" & strDT
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryProperty_Tenant_Data_Dump", strFileName, True
Exit Sub

Still Invalid Argument.  The query works in the QBE window, though.

r

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

> Hi Rocky,
>
> Could you just humour me and change the line
>     strFileName = gstrFrontEndPath & "Property_Tenant_Data_Dump_" & strDT
> To
>     strFileName = gstrFrontEndPath & "Property_Tenant_Data_Dump"
> So we aren't using the date and see if that works.
>
> Paul
>
> On Thu, 31 Mar 2022, 04:09 Paul Hartland, <paul.hartland at googlemail.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
>