List of queries by type

[Eljefegeneo]

Is there any way of getting a list of the queries in a database by type? That is, I would like to have a form that has two combo boxes. One selecting the type of query: Select, delete, append, update, make table. The second comb box would show only those types of queries selected by the first combo box.
I have been using the following to get a list:

SELECT m.Name  [FONT=&quot]FROM MSysObjects AS m[/FONT]
  [FONT=&quot]WHERE m.Type=5 AND m.Name Not ALike "~%"  [/FONT]
  [FONT=&quot]ORDER BY m.Name;[/FONT]

But I can't figure out to set a parameter to select them by type. I really want the list to show only the select queries, but I am curious to know if the query type can be identified.

Any suggestions? Thanks.

 

[Galaxiom]

Look for the Select keyword in the SQL property of the queries. You would need to do this though the QueryDefs Collection.

CurrentDb.QueryDefs("queryname").SQL

 

[jdraw]

I have done this in the past, I'm searching through old databases.
Found part of it.

This should find most/all queries.

  SELECT Name , 'query' as ObjTyp FROM MsysObjects WHERE  [Name] not Like '~*'   AND " _
       & " MSysObjects.Type = 5"

Every querydef has a type attribute.
This is extracted from old code

qdf.Type = 0, "SELECT"
qdf.Type =16, "CrossTab"
qdf.Type = 32, "DELETE"
qdf.Type = 48, "UPDATE"
qdf.Type = 64, "APPEND"
qdf.Type = 80, "MK TBL"
qdf.Type =96, "DDL"
qdf.Type =112, "SQLPassThru"

Hope it's helpful.

Just found this via google (and updated parts of above list)
https://msdn.microsoft.com/en-us/library/office/ff192931.aspx

 

[Galaxiom]

QueryDefType Enumeration from Microsoft

 

[Eljefegeneo]

I spent 30 minutes trying various search criteria and came up with nothing. So thank you. I will see how this can be added to the select statement. Hopefully I can figure it out and not have to ask again for help on this issue.

Thank you both!

 

[Galaxiom]

A strategy I sometimes use to explore an object is to list the members of its Properties Collection and their values. This can be done as a sub but I often just do it in the Immediate Window.

For a query it would be like this in the Immediate Window:

Set db = Currentdb: For Each prpty in db.QueryDefs("queryname").Properties: ? prpty.Name & vbTab & prpty.Value: Next

However in this particular case the StillExecuting property is not valid so it falls over. Leave out prpty.Value to avoid that and just get a list of the Property Names.

 

[Eljefegeneo]

I have worked on this for more than an hour with many variations and cannot figure out how to include the querydef or QueryDefTypeEnum as shown in the third post.

The MsysObjects table does not seem to have anything that I can use for the querydef. So it would seem to me that I am not comprehending how this is done.

I have tried to use only the querydef in the select statement with and without the "type" but the same sad results occur. Is there a reference to

MsysObjects.QueryDef?

I thought of using a union query, first with the code that selects all the queries and then combining with one that selects only those that meet the desired criteria of a select query, but I cannot determine that SQL statement either.

I know in the end that there is a simple solution to this, but I cannot seem to see the trees because of the forest.

 

[Galaxiom]

MSysObjects is accessible through SQL but as you have found, the Type is not included there. The Type is apparently only available via DAO so you have to drill though the object model to get the property.

Consequently to get the Type in a query you would need to create a Public Function to return the property of the QueryDef from its name read from MSysObjects.

 

[jdraw]

Eljefegeneo,

Just a thought, but you could build a table and store only what you need.
You could have a routine that recreates the table to ensure you have all of the latest queries. Then,get the data from the table(via a query) to do what you want. May not end up as combos, but you could probably do that if needed.
I moved all tables, queries, forms etc to a table in my old code.


You would need to create a table (once)
eg

'---------------------------------------------------------------------------------------
' Procedure : CreateTheTable
' Author    : mellon
' Date      : 24/09/2015
' Purpose   : Create a table hold info about each query in this database
'
' Query Name, Query Type and Query SQL
'---------------------------------------------------------------------------------------
'
Sub CreateTheTable()
      Dim SQL As String

10       On Error GoTo CreateTheTable_Error

20    SQL = ("CREATE TABLE tblObjDefsx " _
                   & "(objName text(120), " _
                   & "objType text(50), " _
                   & "objSource memo, " _
                   & "CONSTRAINT MyKeyZ " _
                   & "PRIMARY KEY (objName, objType) );")
30    Debug.Print SQL 'for review
40    CurrentDb.Execute SQL, dbFailOnError

50       On Error GoTo 0
60       Exit Sub

CreateTheTable_Error:

70        MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure CreateTheTable of Module FindSource"
End Sub

Procedure to empty, re-scan and refill the table with latest query info

'---------------------------------------------------------------------------------------
' Procedure : FindQueriesAndType
' Author    : mellon
' Date      : 24/09/2015
' Purpose   : Scan the current database and record each query name, query type and query sql
'             in table tblObjDefsx. Note the table is emptied and refilled with latest info.
'---------------------------------------------------------------------------------------
'
Public Function FindQueriesAndType()

'******* SECTION NOTE *******  
'  Define all the variables used only in this procedure. 

  Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As QueryDef
    Dim DeleteSQL As String

10  On Error GoTo FindQueriesAndType_Error

20  Set dbs = CurrentDb
30  Set rst = dbs.OpenRecordset("tblObjDefsX")

    'Clear previous table entries

40  DeleteSQL = "Delete * from tblObjDefsX;"
50  dbs.Execute DeleteSQL, dbFailOnError

    'set up to refresh/refill the table with query info

60  With rst

        '******************** SECTION NOTE ******************
        ' Collect the SQL for each query object in the database

70      For Each qdf In dbs.QueryDefs
80          If Left(qdf.name, 1) = "~" Or _
               Left(qdf.name, 4) = "MSys" Then
90          Else
100             .AddNew
110             !objName = qdf.name
120             !objsource = qdf.SQL

                ' *******************************************
                ' **          Identify Query ObjTypes      **
                ' *******************************************
130             !ObjType = _
                            IIf(qdf.Type = 0, "SELECT", ( _
                            IIf(qdf.Type = 16, "CROSSTAB", _
                            IIf(qdf.Type = 32, "DELETE", ( _
                            IIf(qdf.Type = 48, "UPDATE", ( _
                            IIf(qdf.Type = 64, "APPEND", ( _   
                            IIf(qdf.Type = 80, "MK TBL",( _         
                            IIf(qdf.Type = 96, "DATA DEFN", ( _
                            IIf(qdf.Type = 128, "SETOP/UNION", qdf.Type))))))))))))))
140             .Update
150         End If
160     Next

170 End With

    '************************ SECTION NOTE ***************** _
     Clean up the variables that were used to release the memory space.
180 Set dbs = Nothing
190 Set rst = Nothing


200 On Error GoTo 0
210 Exit Function

FindQueriesAndType_Error:

220 MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure FindQueriesAndType of Module FindSource"

End Function

If anyone reading this understands how to use the Enums in the code, please tells us how it works and show in revised vba.

 

[Eljefegeneo]

Jdraw:
Finally had a chance to use your code and of course it does exactly as you said it would. So thank you. I hope others will find it equally as useful.

Perhaps someone will see your request at the bottom of your last post!

Thanks again!

 

[jdraw]

Glad it worked for you. Happy to help.

Yes, I hope someone will see that request- in fact - I'll repeat it here.

If anyone reading this understands how to use the Enums in the code, please tells us how it works and show in revised vba.

 

[Galaxiom]

Enums are simply memorably named constants that can be substituted for the numeric values they represent.

eg dbQAppend can be used instead of 64.

I assume you are actually looking for a function that returns a name similar to TypeName() that returns the name of the datatype of the argument. AFAIK there is nothing like QueryTypeName(). However you could easily create that function which would tidy up the code considerably.

 

[Eljefegeneo]

For anyone interested, I found this for the function suggested by Galaxiom

I take no credit for this except to find it. Not sure it it covers all possible types of queries.

http://www.tek-tips.com/viewthread.cfm?qid=1218797

  [FONT=&quot]Public Function basQueryType(ObjType As Long) As String[/FONT]
  [FONT=&quot]Dim tmp As String[/FONT]
  [FONT=&quot]Select Case ObjType[/FONT]
  [FONT=&quot]Case 0[/FONT]
  [FONT=&quot]  tmp = "Select"[/FONT]
  [FONT=&quot]Case 16[/FONT]
  [FONT=&quot]  tmp = "Crosstab"[/FONT]
  [FONT=&quot]Case 32[/FONT]
  [FONT=&quot]  tmp = "Delete"[/FONT]
  [FONT=&quot]Case 48[/FONT]
  [FONT=&quot]  tmp = "Update"[/FONT]
  [FONT=&quot]Case 64[/FONT]
  [FONT=&quot]  tmp = "Append"[/FONT]
  [FONT=&quot]Case 80[/FONT]
  [FONT=&quot]  tmp = "MakeTable"[/FONT]
  [FONT=&quot]Case 96[/FONT]
  [FONT=&quot]  tmp = "DDL"[/FONT]
  [FONT=&quot]Case 112, 144[/FONT]
  [FONT=&quot]  tmp = "PassThrough"[/FONT]
  [FONT=&quot]Case 128[/FONT]
  [FONT=&quot]  tmp = "Union"[/FONT]
  [FONT=&quot]Case 160[/FONT]
  [FONT=&quot]  tmp = "Compound"[/FONT]
  [FONT=&quot]Case 224[/FONT]
  [FONT=&quot]  tmp = "Procedure"[/FONT]
  [FONT=&quot]Case 240[/FONT]
  [FONT=&quot]  tmp = "Action"[/FONT]
  [FONT=&quot]Case Else[/FONT]
  [FONT=&quot]  tmp = "Unknown"[/FONT]
  [FONT=&quot]End Select[/FONT]
  [FONT=&quot]basQueryType = tmp[/FONT]
  [FONT=&quot]End Function[/FONT]

And the SQL for the Query:

  [FONT=&quot]
 [/FONT]   
  SELECT Name, basQueryType([Flags]) AS QueryType
FROM MsysObjects
WHERE Type=5 AND Left([Name],1)<>'~'

Above posted by "PHV" on the web page. Thank you PHV.

 

[jdraw]

Thanks for the link Eljefegeneo. I have a lot of respect for PHV --he has had many interesting posts over the years - I never saw that one though.

However, based on his post I have written the following to take advantage of the QueryDefEnums

'---------------------------------------------------------------------------------------
' Procedure : GetQueryType
' Author    : mellon
' Date      : 25/09/2015
' Purpose   :  Function to identify each query in the database and identify its Type
'              using the M$oft DAO QueryTypeEnumeration values
'---------------------------------------------------------------------------------------
'
Function GetQueryType(QueryDefType As Long) As String
          Dim tmp As String

          'using the  enumeration   DAO.QueryDefTypeEnum

10       On Error GoTo GetQueryType_Error

20        Select Case QueryDefType

          Case DAO.QueryDefTypeEnum.dbQAction
30            tmp = "Action"
40        Case DAO.QueryDefTypeEnum.dbQAppend
50            tmp = "Append"
60        Case DAO.QueryDefTypeEnum.dbQCompound
70            tmp = "Compound"
80        Case DAO.QueryDefTypeEnum.dbQCrosstab
90            tmp = "Crosstab"
100       Case DAO.QueryDefTypeEnum.dbQDDL
110           tmp = "DDL"
120       Case DAO.QueryDefTypeEnum.dbQDelete
130           tmp = "Delete"
140       Case DAO.QueryDefTypeEnum.dbQMakeTable
150           tmp = "MakeTable"
160       Case DAO.QueryDefTypeEnum.dbQProcedure
170           tmp = "Procedure"
180       Case DAO.QueryDefTypeEnum.dbQSelect
190           tmp = "Select"
200       Case DAO.QueryDefTypeEnum.dbQSetOperation
210           tmp = "SetOper/Union"
220       Case DAO.QueryDefTypeEnum.dbQSPTBulk
230           tmp = "SPTBulk"
240       Case DAO.QueryDefTypeEnum.dbQSQLPassThrough
250           tmp = "PassThrough"
260       Case DAO.QueryDefTypeEnum.dbQUpdate
270           tmp = "Update"
280       Case Else
290           tmp = "Unknown"
300       End Select

310       GetQueryType = tmp

320      On Error GoTo 0
330      Exit Function

GetQueryType_Error:

340       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure GetQueryType of Module FindSource"
End Function

You could use the above with the previous set up I had to record query info to a table, OR
you can use the following query to identify the Type of each of the queries in your database.

SELECT Name, GetQueryType([Flags] ) AS QueryType
FROM MsysObjects
WHERE Type=5 AND Left([Name],1)<>'~'

Now the next question ---What is special about [Flags]? I'm sure PHV knows....

I have not seen this used, but it is needed to make the query execute without getting an unknown parameter notification.

Anyway my intention here was to show the use of the Enums.

After the post I found this by Jim D Experts Exchange (2009)

[I][COLOR="RoyalBlue"]Verified Answer

by:Jim DettmanPosted on 2009-06-12 at 06:05:47ID: 24611759

The flags column is undocumented.  However if you poke around on the net, you'll find places where people have taken a stab at documenting them.  Here's one shor list I found:

Append = 64

Crosstab = 16

Data-Definition = 96

Delete = 32

Deleted or temp ( prefixed with ~) = 3

Hidden = 8

Linked Table= 2097152

Make-Table = 80

MSys Table (w/o read permission) = 2

MSys Table (w/ read permission) = -2147483648

Select Query or standard table = 0

Union = 128

Update = 48

 As you can see, most futher define the type of object.  For what your doing, I can't see 
why you'd want to use the flag column.  Checking the type column for 3 will get you all 
the table[/COLOR][/I]

 

[Eljefegeneo]

I did some brief experimenting with the flags and they seem to work OK, at least for the few that I tried. I am going to do more tomorrow - too late now for me. But I guess you can always learn something new. Thanks for the update too!

 

[jdraw]

Eljefegeneo, Galaxiom

I ran my latest code against my database and got an "Unknown" type. A little analysis shows it to be an Access View. I created a sample Access view for a poster recently
see http://www.access-programmers.co.uk/forums/showpost.php?p=1451821&postcount=4
I looked at MSysObjects and note that the "Unknown" has a Flags value of 268435456

So I have modified the most recent code with these 2 lines

272          Case 268435456            ' I found that a View is stored as a query with this flags value
276           tmp = "AccessView"    'this may not be true in all cases??

I don't know for certain that all views would have that Flags value.