Create a New Query from VBA (2003)

Topic

New Reply

I have a form with some controls that let me query tables pretty fast. One of those is a textbox that displays the SQL code of the query shown in a listbox, so I can copy the SQL code, manually create a new query, paste the code, and fine tune it manually. This is also useful for copying the output, which the listbox restricts.
When a button is pressed, the controls feed the procedure, which forms a public strSQL string, which in turn is used to fill both the textbox (with the SQL code shown as text) and the listbox (via its rowsource property).

I’d like to automate the last step. That is, I would like to click on a button and have Access create a new query for me (without actually saving it if possible), paste the strSQL string and run the query. Can this be done?

DoCMD.Openquery seems to work on queries that are already created (and saved).

Thank you in advance.

Reply | Quote

Replies

If it’s the SQL for an action query such as an update query or delete query, you can run the SQL directly using DoCmd.RunSQL (this operates at the Access level) or CurrentDb.Execute (at the Jet Engine level).

You can’t display the records returned by the SQL for a select query directly – you have to create a query and set its SQL property, then open the query, or use the SQL as Record Source for a form or report, and open that form or report.

Reply | Quote

Thank you, Hans.

I think I could finally help myself out with this.

The preliminar code (which is working) is as follows:

Private Sub cmdAbrirConsulta_Click()

Dim qdfNew As DAO.QueryDef

On Error Resume Next
With CurrentDb
.QueryDefs.Delete (“z_Temp”)
Set qdfNew = .CreateQueryDef(“z_Temp”, strSQL)
.Close
End With
DoCmd.OpenQuery “z_Temp”

End Sub

Any further suggestions are welcome!

Reply | Quote

That’s the way to create a query on the fly, setting its SQL.

Reply | Quote

Many thanks, Hans. I really appreciate your valuable help.

Have a nice week!

Reply | Quote