Home  |   French  |   About  |   Search  | mvps.org  

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

In Memoriam

Terms of Use


VB Petition

Queries: Return database object names

Author(s)
Dev Ashish

(Q) How can I retrieve names of database objects (queries /forms/ table/ reports/ modules/ macros) using a query?

(A) The Access system table MsysObjects table contains a list of all database objects. Although undocumented, you can query this table to retrieve the names you want.

Note: Use any Access system table with extreme caution. Unintentional or intentional modifications of any kind in these tables might result in an unusable database.

'Code Courtesy of
'Dev Ashish

'******************** Code Start ************************
Use the following SQL statements as Rowsource for the control depending on which object you want.

Queries:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
(MSysObjects.Type)=5 ORDER BY MSysObjects.Name;

Forms:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
(MSysObjects.Type)=-32768 ORDER BY MSysObjects.Name;

Tables:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
(Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;

Reports:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
(MSysObjects.Type)= -32764 ORDER BY MSysObjects.Name;

Modules:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
(MSysObjects.Type)= -32761 ORDER BY MSysObjects.Name;

Macros:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
(MSysObjects.Type)= -32766 ORDER BY MSysObjects.Name;

'******************** Code End ************************


1998-2010, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer