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

Is this possible?
junoak77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, see here, querying the MSysObjects system table:
http://articles.techrepublic.com.com/5100-9592-5047664.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.