[AccessD] Excel Data Dump

[Rocky Smolin]

Yeah, always. I'm gonna sleep on it.  In the morning maybe something will
come clear.

r

On Wed, Mar 30, 2022 at 10:13 PM Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:

> 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
> >
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>