News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Name:Alex Dybenko

Location:Moscow, Russia

Wednesday, December 09, 2009

How to list queries by type

You can get a list of all queries with simple select statement:

FROM MSysObjects
WHERE Type=5

There is a Flags field in MSysObjects which shows query type, for example you can get a list of all pass-through queries:

FROM MSysObjects
WHERE MSysObjects.Type=5 AND Flags in (112, 144)

And here a list of flags field values you can use (at least what i found):

  • 0 – Select query
  • 3 – hidden select query, used in form’s recordsource or control’s rowsource
  • 16 – Crosstab query
  • 32 – Delete query
  • 48 – Update query
  • 64 – Append query
  • 80 - Make-Table query
  • 96 – DDL query
  • 112 - Pass-through Select query
  • 128 – Union query
  • 144 - Pass-through Action query



Anonymous Anonymous said...

Thanks Alex - this is very helpful. I am using msysobjects for identifiers to present (or hide) specific query or report objects for review and wanted to make sure that action queries were excluded. This tells me exactly how to do that.

Chris Naylor

5:40 PM  

Post a Comment

<< Home