Defining a recordset through SQL in VBA for export to excel

[emorris1000]

Edit: Crud, don't know how I messed this up, this should be in the VBA section. If someone could move it I would appreciate it.

I am working on a series of forms that will have an "export to excel" button. Originally this was a simple thing. Each form had an underlying query that defined it, and I was able to use this:

DoCmd.OutputTo acOutputQuery, "QueryName", "Excel 97 - Excel 2003 Workbook (*.xls)", "ExportFileName", True

With a loop to define export file names as a series of temp files, no biggie.

I'm realizing now, however, that having those queries is cluttering up my database, and for everything except the export function they are unnecessary.

So, question. Is it possible to export a query that is defined solely in VBA?

I *think* I can define a recordset through DAO, but as far as I can tell there is no clear output method for a recordset, only for a query/table/etc.

Also, I'm hoping this will be an easy solution (I know....I know....) because if it's not I can just continue to define hard queries for the system to use.

Take one form. It's simply a graph with a rowsource query. Can I reference that rowsource query as an object and export it cleanly?

 

[MarkK]

Easy is what you know how to do.
To write a query in VBA you can do something like ..

dim qdf as dao.querydef
set qdf = currentdb.createquerydef( _
  "qNewName", _
  "SELECT * FROM YourTable WHERE YourTableID = " & Me.SomeControlOnForm)
DoCmd.OutputTo acOutputQuery, "qNewName", "Excel 97 - Excel 2003 Workbook (*.xls)", "ExportFileName", True
currentdb.querydefs.delete "qNewName"

Cheers,

 

[emorris1000]

Pretty sure this is exactly what I needed, thanks much!