Microsoft Access DoCmd.SaveAs to create another query based on existing query

I have a select query called 'qryDefineGrid'

On Click Event of a form button I wish to create another query based on 'qryDefineGrid'  as though I had physically done a Save As, and then renamed it.  Once done I would then want the user to be taken to Design View of this new query.

The new query would be named as the text that would be entered in a form a control[frmExport]![txtGridQueryName]

There does not appear to be a DoCmd.SaveAS ?

Developing in 2007 that is also used in 2010 environment.
Who is Participating?
Dale FyeConnect With a Mentor Commented:
You can determine the SQL behind a query with syntax similar to:


So, to create a new query based on that query you would use:

Dim strQueryName as string
Dim qdf as dao.querydef

strQueryName = forms("frmExport").txtGridQueryName
set qdf = currentdb.CreateQueryDef(strQueryName, currentdb.QueryDefs("qryDefineGrid").SQL)

Open in new window

Then, to open that query in design view, via code:
docmd.openquery strQueryName, acViewDesign

Open in new window

Paul-bbcAuthor Commented:
Does what it says on the tin
Dale FyeCommented:
"Does what it says on the tin "

Not sure what this means, must be a colloquialism.  Glad I could help.

If you are going to allow users to create queries like this on the fly, you might also create a table to store the names of these queries and allow the user to provide a description of the query.  Then, you could provide a form for them to select a query that they have already created.
Paul-bbcAuthor Commented:
'Does what it says on the tin' old English expression for 'it does exactly what it says it will', taken from an old Dulux tv paint advert.

Already have a table for them to use. There are over 50 customers who want an Excel output of the underlying data, but some only want some columns, some want them in a different order etc., and customers added all the time.  The same user interface then also does an output to command for Excel, referencing the same query name that was created for the other step.
Dale FyeCommented:
Guess I probably could have looked that up, couldn't I.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.