MS ACCESS Getting "Description of the table" through the query

Question

I searched everywhere for a way to access a table description (the same one that appears when I right-click on a table's properties) through a SELECT query.

I tried using MSysObjects, but I can only get the table name using this.

Is it possible to do this with a query or is VBA required?

Answer 1

As Remou says, you cannot get it from the request (but you can include a function that returns it in the request). Here is another function:

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

Here is a query that returns all non-system tables, indicating 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 make an almost identical function for queries. The trick I found is that you return only the inherited descriptions, otherwise if the request has no description, you will get a description of the requested 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 

On Error Resume Next 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 the properties of the TableDef, but I do not think that a standard query will work.

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

Answer 3

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

SELECT MSysObjects.Name, GetQueryDescr ([Name]) Properties AS, MSysObjects.DateCreate, MSysObjects.DateUpdate FROM MSysObjects WHERE ((((MSysObjects.Name) Don't like "~ sq_ *") AND ((MSysObjects.Type) = ;

type 5 for queries

Answer 4

Always reset your error trap in VBA:

  Public Function DoSomething ()
   On Error Resume Next
   ** potential error-throwing code **
   On Error Goto 0
 End function

Otherwise, the error may be skipped elsewhere in your code.