ms-accessvba

Get Query Type property in VBA

Is there a way to identify a query's type in VBA (i.e., Append Query, Select Query, Delete Query, Make Table Query)? I am creating a function with a string parameter (query). It will need to check if the query exists and then its action will depend on what type of query it is.

Solution

  • Examine the query's QueryDef.Type property. It returns a value from the DAO QueryDefTypeEnum Enumeration. (You can also inspect that enum in the Object Browser from the VB Editor instead of looking it up online.)

    Here is an Immediate window session which demonstrates pieces you can use in your VBA code.

    ? DAO.QueryDefTypeEnum.dbQSelect
 0 
? CurrentDb.QueryDefs("qrySelect").Type
 0 
? CurrentDb.QueryDefs("qrySelect").Type = dbQSelect
True
? DAO.QueryDefTypeEnum.dbQDelete
 32 
? CurrentDb.QueryDefs("qryDelete").Type = dbQDelete
True
' qryBogus does not exist, so the next statement throws
' Error 3265: Item not found in this collection. 
? CurrentDb.QueryDefs("qryBogus").Type