Change Table Property Description with VBA

[Gary S]

I want to change the table property description with VBA code.
How should I go about that?

Also, if I wanted just to read the table proeprty description
using VBA, how is that done?

 

[Douglas J. Steele]

CurrentDb.TableDefs("NameOfTable").Properties("Description") will read it,
but only if a description exists. (If one doesn't, you'll get an runtime
error 3270 ("Property not found").

If the property does exist, changing it's value is as simple as:

CurrentDb.TableDefs("NameOfTable").Properties("Description") = "This is the
new description"

If it doesn't, you need to use the CreateProperty method.

Here's some untested code.

Function GetTableDescription(TableName As String) As String
On Error GoTo ErrHandler

Dim strDescription As String

strDescription = CurrentDb.TableDefs(TableName).Properties("Description")

ExitHere:
GetTableDescription = strDescription
Exit Function

ErrHandler:
Select Case Err.Number
Case 3270 ' Property Not Found
strDescription = "No description exists"
Case Else
MsgBox Err.Number & ": " & Err.Description
End Select
Resume ExitHere

End Function

Sub SetTableDescription(TableName As String, Description As String)
On Error GoTo ErrHandler

Dim tdfTable As DAO.TableDef
Dim prpDesc As DAO.Property

CurrentDb.TableDefs(TableName).Properties("Description") = _
Description

ExitHere:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 3270 'Property Not Found
Set tdfTable = CurrentDb.TableDefs("TableName")
Set prpDesc = tdfTable.CreateProperty( _
"Description", dbText, Description)
tdfTable.Properties.Append prpDesc
Case Else
MsgBox Err.Number & ": " & Err.Description
End Select
Resume ExitHere

End Sub


You'd use them as:

Dim strOldDescription As String

strOldDescription = GetTableDescription("TableName")
Call SetTableDescription("TableName", "New description")

 

[Alex Dybenko]

Hi,
if you need just to read property - you can use following syntax:

?currentdb.TableDefs("MyTable").Fields("MyField").Properties("Description")

You can change it the same way, but you have to be sure that property
exists, and if it not - then create it. The same way like here:
http://www.mvps.org/access/general/gen0040.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com