MS ACCESS Retrieving "Table Description" Through Query

Question

I've been looking everywhere for a way of accessing a table's description (same one that appears when you right click a table>table properties) through a SELECT query.

I tried using MSysObjects but I can only retrieve the name of the table using that.

Is it possible to do this through a query or is VBA needed?

Answer 1

As Remou says, you can't get it from a query (but you can include a function that returns it in a query). Here's another function:

Public Function GetTableDescr(stTableName As String) As String
On Error Resume Next
GetTableDescr = CurrentDb.TableDefs(stTableName).Properties("Description").Value
End Function

Here's a query that returns all the non-system tables, with their dates and descriptions (using the function above):

SELECT MSysObjects.Name, msysobjects.datecreate, msysobjects.dateupdate, GetTableDescr([Name]) AS Description
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~*") AND((MSysObjects.Name) Not Like "MSys*") and ((MSysObjects.Type)=1));

Finally, you can do an almost identical function for queries. The trick I found is that you only return non-inherited descriptions, otherwise if a query has no description you get the description of the queried object:

Public Function GetQueryDescr(stQryName As String) As String
On Error Resume Next
If CurrentDb.QueryDefs(stQryName).Properties("Description").Inherited = False Then
    GetQueryDescr = CurrentDb.QueryDefs(stQryName).Properties("Description").Value
End If
End Function

The On Error Resume Next is necessary, because until the object has a description the property is null.

Answer 2

You can get the description from the table schema or from TableDef properties, but I do not think a standard query will work.

Set rs = CurrentProject.Connection.OpenSchema(adSchemaTables, _
     Array(Empty, Empty, "Rules", Empty))
Debug.Print rs!Description