How To Use An SQL Statement In A TransferSpreadsheet Command

[orcinus]

I have some some data i would like to output to an excel ss using a SQL statement in my code. Problem is, I don't know how to refer to that sql statement in the transferspreadsheet statement.

Below I have an example. The strSQL variable in the transferspreadsheet statement is obviously incorrect, but just placed to show what I want..

I know I could do it by creating a table and then referring to that created table in the transferspreadsheet function, but would like avoid this if possible..

Any assistance is appreciated..

strSQL = "SELECT table1.SUBID from Table1 where table1.loc = somecriteria"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strSQL, filepathandname, True

[ajetrumpet]

others might know better, but I think you need an actual object to do it with this command, be it query or table.

[SteveF]

Agree with Ajetrumpet -- You need a database object to export, not code within a VBA module.

However, that object doesn't need to be a table: a query will do just as well. A query in the Queries collection is actually SQL underneath, and if needed, you can use VBA to modify the SQL of a query prior to export:


Dim qdf As DAO.QueryDef, dbs As DAO.Database

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("ExportQuery")
qdf.SQL = "SELECT * FROM WorkTable;"
qdf.Close

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ExportQuery", "ExcelFileName", True


Replace the items above with the actual SQL string you want to use (qdf.SQL), the name of the query and the full path to the Excel file.

Steve

[orcinus]

Agree with Ajetrumpet -- You need a database object to export, not code within a VBA module.

However, that object doesn't need to be a table: a query will do just as well. A query in the Queries collection is actually SQL underneath, and if needed, you can use VBA to modify the SQL of a query prior to export:


Dim qdf As DAO.QueryDef, dbs As DAO.Database

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("ExportQuery")
qdf.SQL = "SELECT * FROM WorkTable;"
qdf.Close

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ExportQuery", "ExcelFileName", True


Replace the items above with the actual SQL string you want to use (qdf.SQL), the name of the query and the full path to the Excel file.

Steve

ok, this worked..

So to clarify, I am initially defining qdf as an existing query in my database ("ExportQuery") and then redefining the SQL behind that query in code (i.e. maybe using different fields, adding criteria etc) using the underlying table of "ExportQuery"? (although it seems I could redefine it using any table? Not just the table underlying "ExportQuery"?)

Thanks for your help!

[SteveF]

ok, this worked..

So to clarify, I am initially defining qdf as an existing query in my database ("ExportQuery") and then redefining the SQL behind that query in code (i.e. maybe using different fields, adding criteria etc) using the underlying table of "ExportQuery"? (although it seems I could redefine it using any table? Not just the table underlying "ExportQuery"?)

Thanks for your help!

The qdf variable in the code is your link to an existing query in the database. The Set statement creates the link to the specified query; after you Close the link you can Set it again to link to some other query if you like.

All "queries" in Access actually use SQL statements to perform their actions -- depending on what they do one could regard them as the Access equivalent of either Views or Stored Procedures in other SQL RDBMSs. There's no reason you can't build a SQL statement in VBA that points to any table/tables in your database and stuff that into the SQL property of any query in your project's query collection. Needless to say, this capability needs to be used with caution: I'd probably have one or two queries in a project that were specifically reserved for this type of use, leaving the others alone to do what they were originally designed for.

Steve

[orcinus]

The qdf variable in the code is your link to an existing query in the database. The Set statement creates the link to the specified query; after you Close the link you can Set it again to link to some other query if you like.

All "queries" in Access actually use SQL statements to perform their actions -- depending on what they do one could regard them as the Access equivalent of either Views or Stored Procedures in other SQL RDBMSs. There's no reason you can't build a SQL statement in VBA that points to any table/tables in your database and stuff that into the SQL property of any query in your project's query collection. Needless to say, this capability needs to be used with caution: I'd probably have one or two queries in a project that were specifically reserved for this type of use, leaving the others alone to do what they were originally designed for.

Steve

ok, thanks for the info..