can I create ad hoc query for users?

[Shannon]

I have created a menu for my users that contains several
buttons - each running a predefined query. I would love
to create a button that will allow users to run ad hoc
queries they may need...can I do that?

Thanks much.
Shannon

 

[Pavel Romashkin]

You are confident your users know SQL? Then you could try something like
this, in the after update event of your text field:

Dim Qry as DAO.Querydef
Set Qry = CurrentDb.CreateQueryDef("NewQuery")
Qry.SQL = Me.ThisTextBox

Open the new query by hand or code and there you have it. But I would
expect the phone ring off the hook within 10 minutes after you
distribute the application... And one day someone will try to type in
"DELETE * FROM tblCustomers" to see if this will work...
Pavel

 

[Shannon]

No....my users do not know SQL or anything else about
database design. I am looking for a very straightforward,
simple form for them to use to select tables and fields
for ad hoc queries. Any thoughts?

Shannon

 

[Pavel Romashkin]

Well... what do you plan they will type in into that text box? If you
know, you can add the rest in code (similar to my first suggestion) to
convert it into a valid SQL statement.
Another option is to make a combo box with the list of tables you allow
them to search, a combo box that will let them select a field from the
table they selected in the first combo and a text box for criteria. Then
they type in the criteria and search. Is this something that may work
for you?
Pavel

 

[Duane Hookom]

Check out the Query By Form at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. It
allows users to select fields, sort, set criteria, group, etc. Results are a
couple clicks from sending to Excel, Html, CSV, Word Merge, complete Form
Letter Report,...