How to access an Access Query Object's SQL string?

[deyken]

Hi All,

I had an unusual request from a client this past week. They have created their own database (sizing up to over 1GB!) and have several sets of Access Queries, Tables, Forms, Reports and so on. They want me to create a form to show - in a dropdown list/combobox - a list of all the Query Objects on the active database, which I successded in getting (with a MsSysObjects query). Now that I have these queries listed in a combobox I need it to actually RUN and deliver its result set to another Listbox residing on the same form.

Does anybody have an idea how to access (in VBA Code specifically) the SQL Query string in each listed Query Object?

I wanted to programmatically declare an AccessObject as a Query (not sure how to, though), and then I wanted to assign it Object Name from the text name in the combobox, find that object, access its query, run the query and show the result set (delivers a filtered list of members with their email addresses and cell phone numbers) in another listbox.

It seem straight forward, but somehow I am not quite able to manage this, yet...

Any help would be appreciated!

[orange]

Here's a routine that may be of some use. It Provies the name and the sql for each query in the current database.


'---------------------------------------------------------------------------------------
' Procedure : ZQueries_SQL
' Author : Jack
' Created : 12/16/2010
' Purpose : To number, list names and the component SQL
' of each query in the database to the immediate wiindow
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Sub ZQueries_SQL()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim i As Integer
On Error GoTo ZQueries_SQL_Error

Set db = CurrentDb
For Each qdf In db.QueryDefs
If Left(qdf.name, 1) <> "~" Then
i = i + 1
Debug.Print i & " ***" & qdf.name & "***" & vbCrLf & qdf.sql
End If
Next

On Error GoTo 0
Exit Sub

ZQueries_SQL_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ZQueries_SQL of Module AccessMonster"
End Sub

Good luck with your project.