5

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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
BadgerBeaz
  • 383
  • 3
  • 7
  • 19

3 Answers3

4

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.

Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • wow, thank you very much, I'll try this and report on how it went, thanks again! – BadgerBeaz May 16 '12 at 14:30
  • @fonzy Note that UDFs (user defined functions) cannot be used outside of MS Access. – Fionnuala May 16 '12 at 14:33
  • 1
    Great solution! A small comment: To get the query descriptions, in the select query change the condition for the object type from (MSysObjects.Type)=1 (for tables) to (MSysObjects.Type)=5 (for queries). Also you can add condition (GetQueryDescr([Name]))<>"" to display only nonempty descriptions. – Vadim Kleyzit Aug 25 '16 at 12:55
3

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
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
0

Using GetQueryDescr() above, you can run this query against the hidden sys table

SELECT MSysObjects.Name, GetQueryDescr([Name]) AS Properties, MSysObjects.DateCreate, MSysObjects.DateUpdate FROM MSysObjects WHERE (((MSysObjects.Name) Not Like "~sq_*") AND ((MSysObjects.Type)=5));

type 5 is for queries

cbaker
  • 1