[AccessD] Excel Data Dump

[Rocky Smolin]

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
>