Print list of queries

[Les Blair]

Is there a way to print a list of all the queries that are part of a DataBase?

 

[odin1701]

Do you mean print a list of the names of the queries, or auto print all of the data in each query?

 

[odin1701]

You could make a table called tblQueryName, and run a report based on that after running this code to fill it:

Dim db As Database, qDef As QueryDef
Dim rs as Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblQueryName")
  
  For Each qDef In db.QueryDefs
      rs.AddNew
      rs!QueryName = qDef.Name
      rs.Update
  Next qDef

That should give you a list of all queries in a table.

 

[Les Blair]

I just want a list of the names, but I am not sure how to use what you sent me. where do I put the code? do I define a tbl first?

 

[Les Blair]

Thank you for your help.

 

[boblarson]

You could make a table called tblQueryName, and run a report based on that after running this code to fill it:

Dim db As Database, qDef As QueryDef
Dim rs as Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblQueryName")
  
  For Each qDef In db.QueryDefs
      rs.AddNew
      rs!QueryName = qDef.Name
      rs.Update
  Next qDef

That should give you a list of all queries in a table.

You don't need to create a table to get this. Just use this query:

SELECT Count(MSysObjects.Name) AS CountOfName
FROM MSysObjects
GROUP BY MSysObjects.Type, Left([Name],1)
HAVING (((MSysObjects.Type)=5) AND ((Left([Name],1))<>"~"));

 

[Les Blair]

Thanks, I will give it a try.

 

[boblarson]

Thanks, I will give it a try.

Oops sorry I gave you the COUNT. I'll modify for the names

 

[boblarson]

Sorry about that - here's the modified query for the names:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((Left([Name],1))<>"~"));

 

[Les Blair]

It worked great! Thanks

 

[odin1701]

Hmm...I wasn't aware you could run queries on system objects. Learn something new every day.

 

[boblarson]

Hmm...I wasn't aware you could run queries on system objects. Learn something new every day.

Yeah, I didn't know that one until about 2 years ago and it has come in handy many times since.

 

[The_Doc_Man]

Yup. System tables are just tables.

But you don't own them even if you own the database. They are always locked for writing from queries and such.

I once wrote a cross-referencer that queried the SYS tables. Still have the code somewhere. The real bear was parsing the VBA to find references from that sucker.