Setting Table Property "Description" in Code

[Guest]

How can I see and set the "Descriotion" Access Table Property in VB code?

The Property is just descriptive comment, but I need it to identify different but very similar tables (e.g. Description = "Economic data for Counties 1969 - 2001 Base on Source xxxx") where "xxxx" is different sources or processing methods. When I create a Table manually, I can set the Descirotion property and it shown on the Table List in main Access screen.

I have written VB code, using both ADOX and Access.Application, to loop thru and display all Table properties. However, "Descscription" does not appear in the Property List nor does another Property contain the descriptive comment.

Is there a way to get at this property frm VB.

Thanks in advance.

Victor

 

[Tim Ferguson]

I have written VB code, using both ADOX and Access.Application, to
loop thru and display all Table properties. However, "Descscription"
does not appear in the Property List nor does another Property contain
the descriptive comment.

The table description is part of the GUI, not the data model, and is
therefore unknown to ADO and ADOX, which are only interested in the data.
AFAIK, it is only visible to DAO and the actual Access GUI. Then again,
unless you are using one of those, you can't read the description either so
it doesn't matter that you can't write it.

The Property is just descriptive comment, but I need it to identify
different but very similar tables (e.g. Description = "Economic data
for Counties 1969 - 2001 Base on Source xxxx") where "xxxx" is
different sources or processing methods. When I create a Table
manually, I can set the Descirotion property and it shown on the Table
List in main Access screen.

Ouch, ouch. This is even worse than storing data in table names and field
names -- it's hiding it in the Description property! Really, you need a
design rethink in order to get the the Source and YearRange fields into the
table (n.b. singular, not plural) proper.

Hope that helps


Tim F

 

[Guest]

Tim - thanks for the reply.

In general, I agree with you that information should not be "hidden" by misusing elements of the data and table definition components of access. However, I think you are reading too much into my example comment -- I just want to document how/when/why the table was created (e.g. "Thurs Afternoon -- try 5 REIS rollback" might be another comment). This is, I think a useful thing to do, and quite proper as part of the Table definition since it is a table level comment.

Actually, I have good reason for wanting use the Table Description Property particularly. In the Table List in the Access GUI, the Description Property is shown. This is very helpful in sorting out different versions, sources, recoding, etc. in the GUI. Also, sometimes I create Tables manually. The Description is a good way to describe what I did to create the table in the first place. Such documentation is never a bad thing.

So, the basic reason I want to set the Table Description Property in code is that this would make code created Tables and manulally created Tables show up the same way in the GUI. Do you know of another way of doing this?

Am I to conclude that there are elements of the GIU that are not visible and settable in VB/VBA code? If not with ADOX or Access 9.0 Object Library (MSACC9.OLB), then with some other DLL? If true, this is a bit aggravating of Microsoft (not for the first time)

Thanks.

Victor

 

[Douglas J. Steele]

Reread what Tim posted. You can't use ADOX because the Description isn't a
data property, and ADOX is only concerned with data. However, you can get to
the property using DAO.

Once you've set a reference to DAO, it's
CurrentDb().TableDefs("NameOfTable").Properties("Description")

Be aware, though, that the property only exists if you've set a description.
If you haven't, you'll get a Property Not Found error when you try to refer
to the code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Am I to conclude that there are elements of the GIU that are not visible

and settable in VB/VBA code? If not with ADOX or Access 9.0 Object Library
(MSACC9.OLB), then with some other DLL? If true, this is a bit aggravating
of Microsoft (not for the first time)

 

[Douglas J. Steele]

Sorry: I missed the fact that you were trying to set the Description, not
just read it.

Sub SetDescription(TableName As String, _
TableDescription As String)

On Error GoTo Err_SetDescription

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim prpDesc As DAO,Property

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs(TableName)
tdfCurr.Properties("Description") = TableDescription

End_SetDescription:
Set prpNew = Nothing
Set tdfCurr = Nothing
Set dbCurr = Nothin
Exit Sub

Err_SetDescription:

' Error 3270 means that the property was not found.
If Err.Number = 3270 Then
Set prpDesc = tdfCUrr.CreateProperty( _
"Description", dbText, TableDescription)
tdfCurr.Properties.Append prpDesc
Else
MsgBox Err.Description & " (" & Err.Number & ")"
End If
Resume End_SetDescription

End Sub

 

[Guest]

Thanks.

Sorry: I missed the fact that you were trying to set the Description, not
just read it.

Sub SetDescription(TableName As String, _
TableDescription As String)

On Error GoTo Err_SetDescription

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim prpDesc As DAO,Property

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs(TableName)
tdfCurr.Properties("Description") = TableDescription

End_SetDescription:
Set prpNew = Nothing
Set tdfCurr = Nothing
Set dbCurr = Nothin
Exit Sub

Err_SetDescription:

' Error 3270 means that the property was not found.
If Err.Number = 3270 Then
Set prpDesc = tdfCUrr.CreateProperty( _
"Description", dbText, TableDescription)
tdfCurr.Properties.Append prpDesc
Else
MsgBox Err.Description & " (" & Err.Number & ")"
End If
Resume End_SetDescription

End Sub