List of of Existing Query Names ---> One Column Table

Is this possible?
junoak77Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
ok, see here, querying the MSysObjects system table:
http://articles.techrepublic.com.com/5100-9592-5047664.html
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about using the Db.QueryDefs collection?
0
 
Kelvin SparksCommented:
Create a table named tblList with the key field Querynames and use the Sub below

Kelvin
Sub QueryList()
 
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim sSQL As String
 
Set db = CurrentDb()
DoCmd.SetWarnings False
For Each qdf In db.QueryDefs
    sSQL = "INSERT INTO tblList (QueryNames) VALUES ('" & qdf.Name & "');"
    DoCmd.RunSQL sSQL
Next
DoCmd.SetWarnings True
 
End Sub

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
junoak77Author Commented:
I did a search for QueryDefs and realize it is a VBA thing.  I am pretty much a MS Acces GUII & Wizard simpleton.  Maybe I need to take a expensive VBA course or something; doubt my employer will pay for...guess the upshot is that the code is pretty straight foward, and I know it is in  modules, but unsure how it is invoke via queries/macros/form, etc.   Guess I will look closer at Northwinds, which has a bit of this.....maybe there should be a sub-zone called MS Acces (limited VBA)....whch of course will not have many answers for non GUI-Wizard Application flexibility
0
 
gnarCommented:
Maybe I'm over simplifying, but what about:

select * from msysqueries where name1 is not null;
0
 
gnarCommented:
Er, rather:

select name1 from msysqueries where name1 is not null;
0
 
Kelvin SparksCommented:
For the non VBA person:

Create the table as per my post above.
Copy the code above and goto Modules and choose New
Paste the code into the module, Click on Tools> References and ensure that Microsoft DAO3.6 is ticked (tick it if not

Then press Ctrl G.
In the windows that opens type ? QueryList and press enter

Then check your new table
0
 
junoak77Author Commented:
OK that worked....i really wanted to do it the code way but I got this message after pasting your code into a module and running a macro:
"The expression you enter has a function name MS Office Access cannot find".....and then.....Action Failed: Macro1, Condition=True, Action Name = RunCode, Arguments=GetMyObjectsList().

I was wondering if I ever get the code going, can one populate a list box with certain query aliases, then have a dynamic listbox(s) that are enable showing what fields, preset criteria per field, and optional groupings a person can select to drill down data.  In other words, create a form that is a more user friendly (but a bit more restrictive)  MS Access query design view with contingent listboxes and radio/check buttons, etc.  Maybe some has already created someone has a  2003 Access template for this that I can tailor for my own purposes.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Grading Comments:
OK that worked....i really wanted to do it the code way but I got this message after pasting your code into a module and running a macro:
"The expression you enter has a function name MS Office Access cannot find".....and then.....Action Failed: Macro1, Condition=True, Action Name = RunCode, Arguments=GetMyObjectsList().

I was wondering if I ever get the code going, can one populate a list box with certain query aliases, then have a dynamic listbox(s) that are enable showing what fields, preset criteria per field, and optional groupings a person can select to drill down data. In other words, create a form that is a more user friendly (but a bit more restrictive) MS Access query design view with contingent listboxes and radio/check buttons, etc. Maybe some has already created someone has a 2003 Access template for this that I can tailor for my own purposes.
0
All Courses

From novice to tech pro — start learning today.