[AccessD] Excel Data Dump

[Paul Hartland]

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
>>
>