Dynamic Query list in Access

[mr_tango]

Hi All,

I am trying to find a method by which I can create an Access form that will list all of the queries that I have created. I then need the user to be able to select the query and run it.

Does anyone have any ideas, coz I haven't got a clue with this one!

Cheers

[RobDog888]

Try enumerating the QueryDefs colection excluding the system
and hidden queries, if any. Populate in a listbox or combo, or ???
On the click event of whatever object you use, DoCmd.OpenQuery or such.

HTH

[mr_tango]

That sounds perfect! I am having problems with getting any queryDef stuff off the ground. When I run the code, VB doesn't know what a queryDef is. Does anyone know what I need to install or reference to get these working?

I have looked on MSDN but I cannot find anything that points me in the right direction.

Cheers

[mr_tango]

Found it!

Microsoft DAO 3.0 Object Library

[mr_tango]

OK, I have got my head around query defs, but I haven't found anything to do with a queryDef collection?

Can anyone point me in the direction of how to access the query def collection?

Cheers

[mr_tango]

OK, I can list the queries and put them in a combo box:

Public Function listQuery() As Boolean
Dim Arlen As Integer
Dim names() As String
 
Set Db = CurrentDb
 
If Db.QueryDefs.Count <> 0 Then
    Arlen = Db.QueryDefs.Count
    ReDim names(0 To Arlen - 1)
    i = 0
    For i = 0 To (Arlen) - 1
        names(i) = Db.QueryDefs(i).name
    Next i
End If
 
For i = 0 To (Arlen) - 1
    If InStr(names(i), "Output") Then
        cboQueryList.AddItem (names(i))
    End If
Next i
 
End Function

Really easy question, but I just can't remember the answer.....how do you run a query?

[mr_tango]

If I try to execute the query using the method below, I get an error message saying that you cannot run a select query!

Any ideas?

Dim qdf As QueryDef
    Set qdf = CurrentDb.QueryDefs(cboQueryList.Text)
    qdf.Execute
    qdf.Close

[Ecniv]

You know the name of the query, so in a display form (which opens up) set the datasource to the query name.

This should work for a list box or a full form. Your choice how you implement it.


Vince

[mr_tango]

Cheers for the idea Vince, but I have implemented a DoCmd...slightly nasty I know, but it needn't be anything too special.

Thanks for the tip though.

Does anyone know how to delete a query?

[RobDog888]

To drop an Access query using ADO...

sSQL = "DROP VIEW qryIDCount;"
Cnn.Execute sSQL