AccessBlog.net

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

About Me Search
Alex
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:

SELECT Name
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:

SELECT Name
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

Labels:

1 Comments:

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

Links to this post:

Create a Link

<< Home