Saving query as .dqy file

[Richard]

I'm using an Excel Macro to call a dataquery.
I can use MS Query to create a .dqy file that is called by the Excel Macro.

However, I would like to use Access to create the SQL, since it is a more
user friendly program.

How can I create a query in Access and save it in .dqy format that can be
called by an Excel macro?

 

[Douglas J. Steele]

As far as I know, it isn't possible.

However, you can always design the query in Access and use that query
through MS Query, thus saving a couple of steps.

 

[Richard]

How do I do that?
I've tried to cut/past the SQL code from Access to Query, but get an error
message.

In my case, the SQL is fairly complicated, linking around 10 tables.

My ultimate goal is to have an Excel macro that first updates the query
conditions, runs the query, analyzes the output, and mails results using
Outlook - all in one step. Right now I have to manually go into Access, pull
up the query, update the criterial (in this cast dates) by hand, manually
export to Excel files, then close Access. Then run Excel macro that analyzes
the data and mails out.

 

[Douglas J. Steele]

Create a query that links the 10 tables and includes all of the fields you
need.

In MS Query, select that query, rather than the 10 tables. (The query will
show up along with all the tables)