Función VBA para obtener el tipo de consulta y SQL para enumerar la información de la consulta de Access para una base de datos de Access

VBA

Módulo estándar

Coloque esta función en un módulo estándar para poder usarla en una declaración SQL o en un código VBA para averiguar el tipo de consulta en su base de datos de Access. Compílela y guárdela antes de usarla.

Nota: Los objetos ocultos tienen una máscara de bits de 8, que se elimina para probar la enumeración. En teoría, se podrían utilizar más bits; si los hay y los conoce, u otros tipos que no se identifican, envíeme un correo electrónico o agregue un comentario en mi LinkedIn. Junio Acceso Acceso ¡Gracias por la publicación del boletín!

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: 
' this code: 
' 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 *******************************************************