[AccessD] Excel Data Dump

[Paul Hartland]

Yes, I cant see anything that would throw it out on the filename, always my
first goto though as I have saw so many problems in the past.

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

> The file name is
>
> C:\Clients\AGR\Property_Tenant_Data_Dump_03-30-22 07_47_44 PM.xls
>
> I'm using almost the exact same code in another XL export which exports the
> data from one property with the addition of w WHERE clause and it works.
>
> 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
>