object types and names in Access database Ms Access Gurus

If you are helped, please return the favor and help support this site, thank you.

Home Articles Code Tools

SQL for Query to List Object Names in Access database

Create a quick list of object types and names in your database. Show names of tables, queries, forms, reports, macros and modules.

Perhaps you need this information for documentation or to expand on for things to do or test?

Behind the scenes, Access keeps track of this information and more in the Microsoft System Objects table called MSysObjects.

You can also count how many object of each type there are.

Summarize object type and count of each

Quick Jump

Download SQL Results Steps Reference Backstory Share Donate

Goto Top  

Download

Download zipped TXT file that you can copy from to get SQL to make qObjex listing objects types and names, and qObjex_Summary to count how many objects each type has: Query_SQL_ListObjects_s4p__TXT.zip

If you have trouble with the download, 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

Goto Top  

SQL

qObjex

Here is SQL to list object types and names. The SWITCH function is used to convert the numeric value for object type to a recognizable word, and also to sort the list logically rather than numerically or alphabetically.

SELECT
Switch([Type]=1,"Table",[Type]=5,"Query",[Type]=-32768,"Form",[Type]=-32764,"Report",[Type]=-32766,"Macro",[Type]=-32761,"Module",[Type]=4,"ODBC Table",[Type]=6,"Linked Table",[Type]=8,"Relationship",[Type]=3,"container") AS ObjectType
, m.Name
, m.Type
, m.Flags

FROM MSysObjects AS m

WHERE
(((m.Type)<>3) AND ((m.Flags)>=0) AND ((Left([Name],1))<>"~" And (Left([Name],1))<>"{" And (Left([Name],1))<>"_") AND ((Left([Name],4))<>"MSys" And (Left([Name],4))<>"MSys"))

ORDER BY
Switch([Type]=1,"10",[Type]=5,"20",[Type]=-32768,"30",[Type]=-32764,"40",[Type]=-32766,"50",[Type]=-32761,"60",[Type]=4,"11",[Type]=6,"12",[Type]=8,"80",[Type]=3,"90",1=1,99) , m.Name;

qObjex_Summary

Here is SQL to count how many objects each type has:

SELECT
Switch([Type]=1,"Table",[Type]=5,"Query",[Type]=-32768,"Form",[Type]=-32764,"Report",[Type]=-32766,"Macro",[Type]=-32761,"Module",[Type]=4,"ODBC Table",[Type]=6,"Linked Table",[Type]=8,"Relationship",[Type]=3,"container") AS ObjectType
, Count(m.Id) AS Nbr

FROM MSysObjects AS m

WHERE
(((m.Type)<>3) AND ((m.Flags)>=0) AND ((Left([Name],1))<>"~" And (Left([Name],1))<>"{" And (Left([Name],1))<>"_") AND ((Left([Name],4))<>"MSys" And (Left([Name],4))<>"MSys"))

GROUP BY
Switch([Type]=1,"10",[Type]=5,"20",[Type]=-32768,"30",[Type]=-32764,"40",[Type]=-32766,"50",[Type]=-32761,"60",[Type]=4,"11",[Type]=6,"12",[Type]=8,"80",[Type]=3,"90",1=1,99)
, Switch([Type]=1,"Table",[Type]=5,"Query",[Type]=-32768,"Form",[Type]=-32764,"Report",[Type]=-32766,"Macro",[Type]=-32761,"Module",[Type]=4,"ODBC Table",[Type]=6,"Linked Table",[Type]=8,"Relationship",[Type]=3,"container")

ORDER BY Switch([Type]=1,"10",[Type]=5,"20",[Type]=-32768,"30",[Type]=-32764,"40",[Type]=-32766,"50",[Type]=-32761,"60",[Type]=4,"11",[Type]=6,"12",[Type]=8,"80",[Type]=3,"90",1=1,99);

Goto Top  

Results

qObjex

Making a query to show object types and names will give you results such as:

image of example running query to list object types and names

qObjex_Summary

Example query to count how many for each object type:

image of example running query to list howmany objects in each type

Goto Top  

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  

Reference

Microsoft Help

Help: Switch Function

free Analyzer

If you want a free tool to tell you a lot more about your database, download and read about it here: Analyzer

Goto Top  

Backstory

I use this quick SQL a lot! Usually I want a list of tables, but sometimes other objects too. You can also link to the MSysObjects table in another database and modify the source to be your linked table ... or specify the IN clause for another database in the SQL Statement.

If you like this page, please let me know, thank you. Donations are always appreciated

Share with others

Here's the link for this page in case you want to copy it and share it with someone:

https://msaccessgurus.com/VBA/Query_SQL_ListObjects.htm

or in old browsers:
http://www.msaccessgurus.com/VBA/Query_SQL_ListObjects.htm

Get Help with Access

Let's connect and team-develop your application together. I teach you how to do it yourself. My goal is to empower you.

While we build something great together, I'll pull in code and features from my vast libraries as needed, cutting out lots of development time. I'll give you lots of links to good resources.

Have you inherited a database that you need to support? Or do you want to create better documentation? I'm happy to help. Email me at training@msAccessGurus.com

~ crystal

the simplest way is best, but usually the hardest to see

Goto Top