ad-hoc query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wgaskill - 30 Jun 2006 21:26 GMT

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.

Reply to this Message

Douglas J. Steele - 01 Jul 2006 00:48 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I must be stupid, but I can't figure out how to do an ad-hoc query in
>Access.
[quoted text clipped - 3 lines]
> choices,
> and then show the records (and their particular fields) retrieved.

Reply to this Message

wgaskill - 03 Jul 2006 18:12 GMT

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?

> You could always create a QueryDef object in code, assign it the SQL
> statement, and then open the query.
[quoted text clipped - 6 lines]
> > choices,
> > and then show the records (and their particular fields) retrieved.

Reply to this Message

Douglas J. Steele - 03 Jul 2006 18:51 GMT

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"

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Doug:
> I created a QueryDef, opened a recordset, and still don't get anything
[quoted text clipped - 11 lines]
>> > choices,
>> > and then show the records (and their particular fields) retrieved.

Reply to this Message

wgaskill - 03 Jul 2006 19:20 GMT

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

> Dim qdfNew As QueryDef
> Dim strSQL As String
[quoted text clipped - 27 lines]
> >> > choices,
> >> > and then show the records (and their particular fields) retrieved.

Reply to this Message

Douglas J. Steele - 03 Jul 2006 21:51 GMT

Afraid I don't understand your comment.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Thanks - the Help talks about not creating new Queries, but handles a
> QueryDef as a Query.
[quoted text clipped - 32 lines]
>> >> > choices,
>> >> > and then show the records (and their particular fields) retrieved.

Reply to this Message