[AccessD] Excel Data Dump

[Stuart McLachlan]

DId you include ",strFileName, True" on those commands?

On 30 Mar 2022 at 19:51, Rocky Smolin wrote:

> Stuart:
> 
> "DoCmd.TransferSpreadsheet acExport,
> acSpreadsheetTypeExcel9,"qryProperty_Tenant_Data_Dump",
> 
> gives an error 3001 - invalid argument.
> 
> strsql = "qryProperty_Tenant_Data_Dump",
> DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,STRsql
> 
> gives the same error. But MsgBox strSQL prints strsql =
> qryProperty_Tenant_Data_Dump
> 
> Thank you.
> 
> Rocky
> 
> On Wed, Mar 30, 2022 at 7:09 PM Stuart McLachlan
> <stuart at lexacorp.com.pg> wrote:
> 
> > Instead of trying to transfer a query named
> > "qryProperty_Tenant_Data_Dump",
> > you are trying to transfer a table or query named:
> > "SELECT tblProperty.*, tblTenant.* FROM tblProperty LEFT JOIN
> >  tblTenant ON tblProperty.fldPropertyID = tblTenant.fldPropertyID
> >  ORDER BY tblTenant.fldPropertyID, tblTenant.fldTenantSuiteNumber;*"
> >
> > Try:
> > strsql = "qryProperty_Tenant_Data_Dump",
> > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,STRsql
> > OR DoCmd.TransferSpreadsheet acExport,
> > acSpreadsheetTypeExcel9,"qryProperty_Tenant_Data_Dump",
> >
> > On 30 Mar 2022 at 18:40, Rocky Smolin 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
>