VBA function to Get Query Type in words, and SQL to list Access Query information for an Access database.

Quick Jump

Download

Download zipped BAS file that you can import with a function to expand the Query flags into straightforward text: mod_GetQueryType_s4p__BAS.zip

If you have trouble with the downloads, you may need to unblock the ZIP file, aka remove Mark of the Web, before extracting the file. Here are steps to do that: https://msaccessgurus.com/MOTW_Unblock.htm

VBA

Standard module

Put this function into a standard module so you can use it in an SQL statement or VBA code to find out query type in your Access database. Compile and Save before using.

Note: Hidden objects have a bitmask of 8, which is peeled off to test the enum. Theoretically, there could be more bits used; If there are and you know them, or other types that aren't identified, please email me or add a comment on my LinkedIn June Access Access newsletter post, thanks!

Option Compare Database 
Option Explicit 

' module: mod_GetQueryType_s4p
'*************** Code Start ***************************************************
' Purpose  : get Query Type for a Query from MSysObject table or DAO
'              MSysObjects.Flags is combination of bitmask and enum
'              dao.QueryDef.Type is just the enum, doesn't have HIDDEN
' Author   : crystal (strive4peace) with insight from Adrian Bell
' Code List: https://msaccessgurus.com/code.htm
' this code: https://msaccessgurus.com/VBA/Fx_QueryType_SQL.htm
' LICENSE  :
'   You may freely use and share this code, but not sell it.
'   Keep attribution. Mark your changes. Use at your own risk.
'--------------------------------------------------------------------------------

Public Function GetQueryType_s4p( _ 
   ByVal pnFlags As Long _ 
   ,Optional pbAbbreviate As Boolean = False _ 
   ) As String 
'240412 s4p, 240615, 240629, 240706: 262144 from Kent
'240707 from Colin
'return string for query type
'source data is MSysObjects.Flags or DAO.QueryDef.Type

   'PARAMETERs
   '  pnFlags - from MSysObjects
   '         or QueryDef.Type
   '     pass as value since it's modified
   '  pbAbbreviate = True to abbreviate terms (default is FALSE)
   
   'set up Error Handler
   On Error GoTo Proc_Err 
 
   Dim iHidden As Integer _ 
      ,sExtra As String _ 
      ,sQueryType As String 
      
   iHidden = 8  'true if object hidden in Navigation Pane
 
   '------------------------------ Bit mask
   If (pnFlags And iHidden) = iHidden Then 
      sExtra = IIf(pbAbbreviate, ", H", ", Hidden") 
      'this part is a bitmask, not enum, as with the rest of the Flags values
      'modify the passed parameter for flags
      pnFlags = pnFlags And Not iHidden  'Adrian
      'effectively: pnFlags - iHidden
   Else 
      sExtra =  "" 'to be explicit
   End If 

   '------------------------------ ENUM
   Select Case pnFlags  'this part is an enum
   Case dbQSelect   '0 Select
      sQueryType = IIf(pbAbbreviate, "Sel", "Select") 
   Case dbQCrosstab   '16 Crosstab
      sQueryType = IIf(pbAbbreviate, "xTab", "Crosstab") 
   Case dbQDelete   '32 Delete
      sQueryType = IIf(pbAbbreviate, "Del", "Delete") 
   Case dbQUpdate   '48 Update
      sQueryType = IIf(pbAbbreviate, "Up", "Update") 
   Case dbQAppend   '64 Append
      sQueryType = IIf(pbAbbreviate, "App", "Append") 
   Case dbQMakeTable   '80 MakeTable
      sQueryType = IIf(pbAbbreviate, "Make", "MakeTable") 
   Case dbQDDL   '96 DDL
      sQueryType = IIf(pbAbbreviate, "Ddl", "DDL") 
   Case dbQSQLPassThrough   '112 PassThru
      sQueryType = IIf(pbAbbreviate, "PThru", "PassThrough") 
   Case dbQSetOperation   '128 Union
      sQueryType = IIf(pbAbbreviate, "Union", "Union") 
   Case dbQSPTBulk   '144 Bulk
      sQueryType = IIf(pbAbbreviate, "Bulk", "Bulk") 
   Case dbQCompound   '160 Compound
      sQueryType = IIf(pbAbbreviate, "Comp", "Compound") 
   Case dbQProcedure   '224 Procedure
      sQueryType = IIf(pbAbbreviate, "Proc", "Procedure") 
   Case dbQAction   '240 Action
      sQueryType = IIf(pbAbbreviate, "A", "Action") 
	  
   Case 262144 'Complex Select query - Kent Gorrell, Colin Riddington
	  ' has attachment / MVF or column history
      sQueryType = IIf(pbAbbreviate, "complex", "Complex")	 
   Case 3 'temp query - Colin Riddington
      sQueryType = IIf(pbAbbreviate, "temp", "Temp")
	  
   Case Else 
      sQueryType = pnFlags 
   End Select 
   
   sQueryType = sQueryType & sExtra 
   GetQueryType_s4p = sQueryType 
 
Proc_Exit: 
   On Error GoTo 0 
   Exit Function 
 
Proc_Err: 
   MsgBox Err.Description,, _ 
        "ERROR " & Err.Number _ 
        &  "   GetQueryType_s4p"
 
   Resume Proc_Exit 
   Resume 
 
End Function 
'*************** Code End *******************************************************

SQL

EXAMPLE SQL - to copy and use:

1. Compile and save module with GetQueryType_s4p function
2. Make a new query with each of these SQL statements
3. Run! query and best-fit columns

qList_Query_Name_Type_s4p

list query names and types

SELECT mO.Name AS QryName
   , GetQueryType_s4p([mO].[Flags]) AS QryType
FROM MSysObjects AS mO
WHERE (((Left([mo].[Name],1)) Not In ("~","{")) 
   AND ((mO.Type)=5))
ORDER BY mO.Name;

qList_Query_SourceTables_s4p

list source tables and aliases for each query ( Tbl means source and isn't necessarily a table object).

Each query could have multiple records, depending on how many source tables it has.

SELECT mO.Name AS QryName
   , mQ.Name1 AS TblNameOrConnection
   , mQ.Name2 AS TblAlias
   , GetQueryType_s4p([mO].[Flags]) AS QryType
   , mQ.Attribute
  FROM MSysObjects AS mO
    INNER JOIN MSysQueries AS mQ
    ON mO.Id = mQ.ObjectId
  WHERE (((mQ.Name1) Is Not Null)
    AND ((mQ.Attribute)=5 Or (mQ.Attribute)=1)
    AND ((Left([mo].[Name],1)) Not IN ("~","{")))
  ORDER BY mO.Name
   , mQ.Name1;

Steps to create a new query from SQL

1. Create ribbon
2. from Queries group, choose Query Design
3. right-click in top pane for sources and, from shortcut menu choose SQL View, or switch to SQL View using the Home or Query Design ribbon
4. copy the SQL and paste
   be sure not to keep any characters before the word SELECT, or after the terminating semi-colon ;
5. switch to Datasheet View to look at results
6. save the query if you want

Goto Top  

Goto Top