Meaning of MsysObjects values -32758, -32757 and 3 (Microsoft Access)

I'm quering the table MsysObjects for making a list of the objects in my database:

SELECT MsysObjects.Name, MsysObjects.Type
FROM MsysObjects开发者_StackOverflow
WHERE (((Left$([Name],1))<>'~') AND ((Left$([Name],4))<>'Msys'))
ORDER BY MsysObjects.Name;

I know the meaning of this values:

-32768 = Form
-32766 = Macro
-32764 = Report
-32761 = Module
1 = Table
5 = Query
6 = Linked Table

But what about -32758, -32757 and 3? Where do they stand for? Cannot find it on the web.

Type   TypeDesc
-32768  Form
-32766  Macro
-32764  Reports
-32761  Module
-32758  Users
-32757  Database Document
-32756  Data Access Pages
1   Table - Local Access Tables
2   Access Object - Database
3   Access Object - Containers
4   Table - Linked ODBC Tables
5   Queries
6   Table - Linked Access Tables
8   SubDataSheets

-- http://www.access-programmers.co.uk/forums/showthread.php?t=103811

I'd tend to avoid mucking about with the system tables. For one, temporary objects can show up there and confuse things. To get the same information, you can use:

  CurrentDB.TableDefs
  CurrentDB.QueryDefs
  CurrentProject.AllForms
  CurrentProject.AllReports
  CurrentProject.AllMacros

That's the documented way to get the information. Depending on undocumented system tables is not recommended.

Step 1 Create table "AccessObjectType" Columns:

• TypeID autonumber
• TypeStr text
• ShowUser Yes/No
• ShowAdmin Yes/No

Populate the "AccessObjectType" table:

TypeID  TypeStr ShowUser    ShowAdmin

-32775  Module  False   True
-32772  Report  True    True
-32768  Form    True    True
-32766  Macro   True    True
-32764  Report  True    True
-32761  Module  False   True
-32758  User    False   False
-32757  Database Document   False   False
1   Table (local)   True    True
2   Access Object - Database    False   True
3   Access Object - Container   False   True
4   Table, linked ODB SQL   True    True
5   Query   True    True
6   Table, Linked Access Excel  True    True
7   Type 7  True    True
8   SubDataSheet    True    True

If you like, you can create another column called "SimpleType" for grouping of 1, 4 and 6 as simple type Table.

Step 2 Create Query "AccessObjectQ" SQL:

SELECT MSysObjects.Id, 
       MSysObjects.Name, 
       [Name] & " (" & [TypeStr] & ")" AS NameAndType, 
       [TypeStr] & ": " & [Name] AS TypeAndName, 
       Abs([Type]) AS ObjTypeID, 
       AccessObjectType.TypeStr, 
       AccessObjectType.ShowUser, 
       AccessObjectType.ShowAdmin
FROM MSysObjects LEFT JOIN AccessObjectType 
  ON MSysObjects.Type = AccessObjectType.TypeID
WHERE (((MSysObjects.Name) Not Like "msys*" 
  And (MSysObjects.Name) Not Like "*subform" 
  And (MSysObjects.Name) Not Like "*_*" 
  And (MSysObjects.Name) And (MSysObjects.Name) Not Like "*SF" 
  And (MSysObjects.Name) Not Like "*SQ" 
  And (MSysObjects.Name) Not Like "*~*") 
  AND (((AccessObjectType.ShowUser)=True) OR ((AccessObjectType.ShowAdmin)=True)))
ORDER BY MSysObjects.Name;

Step 3 Create a table "AccessObjectVisibility" Columns:

• ObjectID as long (create as a lookup using AccessObjectQ)
• UserVisible as Yes/No
• AdminVisible as Yes/No

You can now easily open this table (or a query) and select objects that you want users and admins to see in automatically populated lists you provide on forms.

Step 4 Create Query "UserAccessObject"

Select * from AccessObjectVisibility where UserVisible = True

Create Query "UserAccessForm"

Select * from AccessObjectVisibility where UserVisible = True and TypeID = -32768

Create queries: "UserAccessReport", "UserAccessQuery", "UserAccessMacro", etc.

Step 5" Create a custom reports menu using query "UserAccessReport" to populate a list box or combo box

FWIW - IIF is a drag, and the switch solution doesn't seem valid for SQL (I may have done something wrong). I entered the values that Fionnuala offered into a new table named AccessObjectXref:

ID  ObjectType  ObjectDesc
1   -32768  Form
2   -32766  Macro
3   -32764  Report
4   -32761  Module
5   -32758  Users
6   -32757  DB Document
7   1   Table
8   2   DB
9   3   Container
10  5   Query
11  8   Subdatasheet

Then used the following SQL to create a list of object names and their counts. Obviously you could include every record if you wanted:

SELECT objectdesc, Count(*) AS Expr1
FROM msysobjects,  AccessObjectTypeXref where type = objecttype
group by objectdesc order by objectdesc

Re David Fenton's proper answer above, here is Microsoft documentation of those techniques. There appear to be two distinct object models for Access:

• Data access object model:
  • Database object has properties TableDefs and QueryDefs, which are collections of all the tables and queries in the database.
• Access object model:
  • CurrentData object has properties AllTables and AllQueries, which are collections of all the tables and queries in the database, with different sets of properties than TableDefs and QueryDefs have.
  • CurrentProject object has properties AllForms, AllReports, AllMacros, and AllModules, which are collections of those objects.