VBA List Only Select Query Names to Debug Window

[Trevor G]

The following code will list all the query names, but I want to list only the select query type or if possible as an alternative list the query names and what type of queries they are.

Reason a lot of queries that have to be edited but need to do the select queries first.

Function dmwListAllQueries() As String
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim strMsg As String
Dim qry As AccessObject, dB As Object

On Error GoTo Error_Handler

Set dB = Application.CurrentData
For Each qry In dB.AllQueries
Debug.Print qry.Name
Next qry

strMsg = " -- Queries listing complete -- "

Procedure_Done:
dmwListAllQueries = strMsg
Exit Function

Error_Handler:
strMsg = Err.Number & " " & Err.Description
Resume Procedure_Done

End Function

 

[MSAccessRookie]

I do not know if this is exactly what you are looking for, and the information that is contained here may or may not be valid for your version of MS Access, but this link is valid for MS Access 2003, and could be a good start toward what you are looking for.

http://accessblog.net/2009/12/how-to-list-queries-by-type.html

 

[Pat Hartman]

run this query to see what kind of queries you have:
SELECT MSysObjects.Type, MSysObjects.Flags
FROM MSysObjects
GROUP BY MSysObjects.Type, MSysObjects.Flags
HAVING (((MSysObjects.Type)=5));

Access doesn't publish descriptions of the system tables because they are for internal use only. But sometimes I use them to document objects in my database. Based on a guess, this is what I have found in my databases:

Flag	FlagDesc
-2147352256	Contains Attachment Data ?
0	Select
3	Embedded Select Queries
16	Crosstab
32	Delete
48	Update
64	Append
80	Make Table
112	Pass-Through
128	Union
144	DDL
262144	?

I'm using A2010 and this is the first time I've seen the big negative number and 262144. They both look like select queries. I made a guess regarding the first one because its name is even different in MSysObjects than it is in the Nav Pane. I have no clue what the last one means. The queries look like normal select queries to me.

 

[Trevor G]

Thank you both this definately will help me go through each query from start to finish starting with select queries. Slight editing but large lists.