ad-hoc query

[Guest]

I must be stupid, but I can't figure out how to do an ad-hoc query in Access.
I can generate the SQL statement in VBA, but I can't get it to pop up like a
regular query. I need to generate the SQL statement based on user choices,
and then show the records (and their particular fields) retrieved.

 

[Douglas J. Steele]

You could always create a QueryDef object in code, assign it the SQL
statement, and then open the query.

 

[Guest]

Doug:
I created a QueryDef, opened a recordset, and still don't get anything
displayed. How do I get the query to display the records?

 

[Douglas J. Steele]

Dim qdfNew As QueryDef
Dim strSQL As String

strSQL = "SELECT ...."

On Error Resume Next
Set qdfNew = CurrentDb().QueryDefs("MyQuery") I

If Err.Number = 3265 Then ' "Item not found in this collection:
Set qdfNew = CreateQueryDef("MyQuery", strSQL)
Else
qdfNew.SQL = strSQL
End If

DoCmd.OpenQuery "MyQuery"

 

[Guest]

Thanks - the Help talks about not creating new Queries, but handles a
QueryDef as a Query.

 

[Douglas J. Steele]

Afraid I don't understand your comment.