Export query results

[Guest]

Can someone advise if query results can be exported without saving the results.

Regards

 

[Douglas J. Steele]

As in "without saving the results in a temporary table"? Sure. Any of the
methods like TransferSpreadsheet or TransferText can use a query rather than
a table.

 

[Guest]

Query information aren't saved as such since they are an alternative way of
looking at the tables. I use this in the "on click" of a command button:


Dim sToday As String

sToday = Format(Now, "dd.mm.yyyy")

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "QUERY NAME", _
"C:\DESTINATION - " & sToday & ".xls", , "SHEET NAME"


This command can be used repeatedly for any number of sheets in an Excel
workbook. I have included the method for including the date the file was
saved.

 

[Guest]

Please advise where the TransferSpreadsheet function is.

Thanks.

 

[Guest]

It is VBA code.

Please advise where the TransferSpreadsheet function is.

Thanks.

 

[Guest]

There is an export button that I can use to export the query results. However
it appears that you need save the query before exporting.

The transferspreadsheet function needs to know the name of the object being
exported therefore is must need to be saved. Please advise.

Thanks

 

[Guest]

I would have thought the results can't be exported unless the query is saved
first.

Otherwise I can't help apart from the code I gave you.

 

[Guest]

If the queries need to be saved then can you suggest a way to view the
queries without opening the database window, so that the user can delete the
query after being exported. I am trying to protect the database by avoiding
the user having access to the database window.

Thanks in advance.

 

[Douglas J. Steele]

To delete a given query, use:

CurrentDb.QueryDefs.Delete "NameOfQuery"

That will raise an error 3265 ("Item not found in this collection") if the
named query doesn't actually exist, so you might want to add error handling
for that case.