Run queries made by code

[Ari A]

Dear Access experts,
When a user hits a button I would like to run a select
query made by VBA .
The code must generate a new query and display the result
on screen. When closing the query may it not be left in
Access.
Is it possible to run a select query which design is only
located in VBA?
I sent in similar enquiry few days ago, got one fine
answer but it left a query in Access after the code ran.

Ari A.

 

[John Vinson]

Dear Access experts,
When a user hits a button I would like to run a select
query made by VBA .
The code must generate a new query and display the result
on screen. When closing the query may it not be left in
Access.
Is it possible to run a select query which design is only
located in VBA?
I sent in similar enquiry few days ago, got one fine
answer but it left a query in Access after the code ran.

You can use the syntax

Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim rs As DAO.Recordset
Set db = CurrentDb
<set strSQL to be your desired query>
Set qd = db.CreateQuerydef("", strSQL)
Set rs = qd.OpenRecordset

This gives you a recordset which you can use in code to inspect the
values returned by the query, etc.

You can display the values in the query onscreen by setting the
Recordsource property of a Form to the SQL string. However, you do
need to have controls on the form for each field you want to see - so
if you can't predict which or how many fields the query will return
it's a fair bit of work.

The alternative would be to use a non-empty string in the
CreateQuerydef method (which actually does save the query) but then
delete it:

Set qd = db.CreateQuerydef("tmpQ", strSQL)
DoCmd.OpenQuery "tmpQ"
db.Querydefs.Delete "tmpQ"