Table Description in Database Window

[Jack Cannon]

I am attempting to set the description of a table that
appears in the database window of an Access application.
(not to be confused with the description that appears
in the table properties dialog box when viewing a table
in design mode)

Normally this is a matter of typing in the desired text
after selecting the properties icon on the toolbar.
However I am attempting to load the desired text into
the description property via VBA code.

The following code works as long as there is some
text already in the description.

Dim dbs As Database
Set dbs = CurrentDb
dbs.TableDefs("MyTable").Properties
("Description") = "Text of my description"

However if there is no pre-existing text in the
description, I
get the following error when the third line of the above
code
is executed.

err.number = 3270
err.description = Property not found.

It seems as though the description property does not
exist unless it
is initialized somehow.

Does anyone know what is going on here and what has to be
done
to accomplish the objective?

Thanks much,
Jack Cannon

 

[Joe Fallon]

You need code like this:

ChangeProperty "StartupShowDBWindow", dbBoolean, False


Function ChangeProperty(stPropName As String, _
PropType As DAO.DataTypeEnum, vPropVal As Variant) _
As Boolean
' Uses the DDL argument to create a property
' that only Admins can change.
'
' Current CreateProperty listing in Access help
' is flawed in that anyone who can open the db
' can reset properties, such as AllowBypassKey
'
On Error GoTo ChangeProperty_Err

Dim db As DAO.Database
Dim prp As DAO.Property

Const conPropNotFoundError = 3270

Set db = CurrentDb
' Assuming the current property was created without
' using the DDL argument. Delete it so we can
' recreate it properly
db.Properties.Delete stPropName
Set prp = db.CreateProperty(stPropName, _
PropType, vPropVal, True)
db.Properties.Append prp

' If we made it this far, it worked!
ChangeProperty = True

ChangeProperty_Exit:
Set prp = Nothing
Set db = Nothing
Exit Function

ChangeProperty_Err:
If Err.Number = conPropNotFoundError Then
' We can ignore when the prop does not exist
Resume Next
End If
Resume ChangeProperty_Exit
End Function