Question Access Object's property description

[pdow]

I'm working on a query that lists all the queries in an Access database, and I would like the query to show the object description which is displayed when you right-click an object and display the object's properties. For queries, this is a text box just below the query name in the properties window.

So far, all I have is:

SELECT ID, Name FROM mSysObjects;

I would like to have something like:

SELECT ID, Name, Description FROM mSysObjects;

 

[NeutronFlux]

Not really familiar with what you're doing, but if you want to get all the fields in mSysObjects, then:

Function GetTableFields(strTable As String)
    Dim r As DAO.Recordset
    Dim fld As Field
    Set r = CurrentDb.OpenRecordset("SELECT * FROM [" & strTable & "]")
    
    For Each fld In r.Fields
        Debug.Print fld.Name
    Next fld
            
End Function

GetTableFields "mSysObjects"

Connect
Database
DateCreate
DateUpdate
Flags
ForeignName
Id
Lv
LvExtra
LvModule
LvProp
Name
Owner
ParentId
RmtInfoLong
RmtInfoShort
Type

Edit: Actually, why can't you just run a query for "SELECT * FROM mSysObjects" to look at everything from mSysObjects and see what you need?

 

[jdraw]

This query will get Tables, Queries, Forms, Reports, Macros and Modules from mSysObjects
but it does not deal with the Description Property.

SELECT MyList.ObjTyp, MyList.Name 
  FROM (SELECT Name, 'table' AS ObjTyp 
 FROM MSysObjects 
 WHERE ([Name] not Like '~*'  AND [Name] not Like 'MSys*'  AND MsysObjects.Type=1)
  UNION
  SELECT Name , 'query' as ObjTyp FROM MsysObjects WHERE  [Name] not Like '~*'   AND 
  MSysObjects.Type = 5
  UNION SELECT MSysObjects.Name, 'form' as objTyp FROM MsysObjects WHERE [Name] not Like '~*'   AND 
 (MSysObjects.Type)=-32768 
 UNION SELECT MSysObjects.Name, 'report' as objtyp FROM MsysObjects WHERE [Name] not Like '~*'   AND 
 (MSysObjects.Type)= -32764 
  UNION SELECT MSysObjects.Name,'module' as objtyp FROM MsysObjects WHERE [Name] not Like '~*'   AND 
 (MSysObjects.Type)= -32761 
 UNION SELECT MSysObjects.Name, 'macro' as objtyp FROM MsysObjects WHERE  [Name] not Like '~*'   AND 
 (MSysObjects.Type)= -32766  ) AS MyList 
  GROUP BY MyList.ObjTyp, MyList.Name ORDER BY MyList.ObjTyp DESC;

 

[pdow]

Thanks everyone.

I couldn't find the description in the mSysObjects fields. There is an LvProp field which contains some "long binary data", which when I extracted it was not the description.

From another thread in this forum, I figured I could get at least the description for queries by using VBA:

Function QueryDescr(QueryName As String)
    On Error Resume Next
    QueryDescr = CurrentDb.QueryDefs(QueryName).Properties("Description")
    If Err Then QueryDescr = ""
End Function

For some reason, not all queries have a "Description" property, hence the On Error.

Then I use that in a query:

SELECT mSysObjects.Name, QueryDescr(Name) AS Description
FROM mSysObjects
WHERE mSysObjects.Type=5 AND Left$(Name,1)<>"~"
ORDER BY 3;

Object type 5 is a query. There are a ton of queries that don't show up in the normal object pane that start with tilde ("~"), so I omitted those. It's slow, but it does the job.