How to programmatically edit table and column comment fields

[Siegfried Heintze]

How do I write a program to CRUD (create, read, update & delete) comment
fields in columns, tables and other entities?
Thanks
Sieg

 

[Allen Browne]

If the Description property does not exist, use CreateProperty().
To read or modify it, check the object's Properties("Description").
To remove it use the Delete method from the object's Properties.

For an example of reading the Description property for all fields in a
table, see:
http://allenbrowne.com/func-06.html

The function below shows how to create and/or set the Description for a
field in a table. Example usage:
Call SetPropertyDAO(dbEngine(0)(0).TableDefs("MyTable").Fields("MyField"), _
"Description", dbText, "A meaningless description")

Function SetPropertyDAO(obj As Object, strPropertyName As String, _
intType As Integer, varValue As Variant, Optional strErrMsg As String) As
Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.

If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True

ExitHandler:
Exit Function

ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to
" & varValue & ". Error " & Err.Number & " - " & Err.Description & vbCrLf
Resume ExitHandler
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function

 

[Siegfried Heintze]

Thank you.
Should this technique work when importing DAO into VB.NET? Can it work with
ADOX instead of DAO.

Here is what I tried with DAO and VB.NET:

I have created a small database with a single table and a single field with
a comment for that field. I have the following loop

Dim prpLopp as DAO.Property
ForEach prpLoop In oField.Properites ' dies here


next prpLoop

It dies with "unandled exception of type
System.Runtime.InteropServices.COMException occured in EnumDB.exe

Additional information: Object invalide or no longer set".

So I inserted a catch statement and printed out

catch ex as System.Runtime.InteropSercices.COMException
MsgBox( ex.ToString("X") )
end try

The value is 800A0D5C

Also, when I fetch oField.Properties.Count I get zero.

I know this has a comment for this field because I can see it with MSAccess.

I know the value of oField is correct because I print out oField.Name and
that is correct.

Thanks
Siegfried

 

[Allen Browne]

You can read Column.Properties("Description") with ADOX if you prefer.

Haven't tried using a DAO reference in VB.NET.

 

[Siegfried Heintze]

Ah hah! They were there all along using ADOX (column comments, that is).

Should table comments work the same way? For tables I can get the date
modified, Date Created, Type. The properties are empty, however. There is
nothing with the value "description".

Thanks,
Siegfried

 

[Allen Browne]

You should be able to get the Description for a field in ADOX with:
Column.Properties("Description")

You may find that Access 2000 and later do not maintain the CreateDate etc.

 

[Siegfried Heintze]

You should be able to get the Description for a field in ADOX with:

Column.Properties("Description")

You may find that Access 2000 and later do not maintain the CreateDate etc.

I'm confused. As I previously explained, I can get the field (column)
commnents using ADOX. It is the table comments I'm having trouble with. I
know my variable oTable is good because I'm gettting reasonable values for
the creation date and modification date.

Siegfried


Here is my code. Neither of the loops execute.

Public oTable As ADOX.Table
.....
Dim ii As Integer
Dim oProp As ADOX.Property
' This never executes
For Each oProp In oTable.Properties
MsgBox("name=" & oProp.Name.ToString() & " value=" &
oProp.Value & " type=" & oProp.Type.ToString & " attr=" &
oProp.Attributes.ToString())
Next oProp

' This never executes either!
For ii = 0 To oTable.Properties.Count - 1
Dim xmlProp As XmlElement =
xmlTag.OwnerDocument.CreateElement("property")
xmlTag.AppendChild(xmlProp)
Dim xmlAttr4 As XmlAttribute =
xmlTag.OwnerDocument.CreateAttribute("name")
xmlAttr4.Value = oTable.Properties(ii).Name.ToString()
xmlProp.Attributes.Append(xmlAttr4)
Dim tnPropN As TreeNode =
tnProperties.Nodes.Add(oTable.Properties(ii).Name.ToString())
Dim tnAttr As TreeNode = tnPropN.Nodes.Add("Attributes")

tnAttr.Nodes.Add(oTable.Properties(ii).Attributes.ToString())
Dim xmlAttr5 As XmlAttribute =
xmlTag.OwnerDocument.CreateAttribute("attributes")
xmlAttr5.Value = oTable.Properties(ii).Attributes.ToString()
xmlProp.Attributes.Append(xmlAttr5)
Try
Dim s As String = oTable.Properties(ii).Value.ToString()
tnPropN.Nodes.Add("Value").Nodes.Add(s)
Dim xmlAttr6 As XmlAttribute =
xmlTag.OwnerDocument.CreateAttribute("value")
xmlAttr6.Value = s
xmlProp.Attributes.Append(xmlAttr6)
Catch : End Try
Dim tnType As TreeNode = tnPropN.Nodes.Add("Type")
tnType.Nodes.Add(oTable.Properties(ii).Type.ToString())
Dim xmlAttr7 As XmlAttribute =
xmlTag.OwnerDocument.CreateAttribute("type")
xmlAttr7.Value = oTable.Properties(ii).Type.ToString()
xmlProp.Attributes.Append(xmlAttr7)
Next

 

[Allen Browne]

Sorry, Siegried, AFAIK, you can get the Description for a Column, but not
for the Table with ADOX.

You may already know how to get the table's Description with DAO (assuming
it has one):
? dbEngine(0)(0).TableDefs("MyTable").Properties("Description")

To confuse matters further, if you try to get the Description for a QueryDef
that has no Description, Access can lie to you and report the Description of
the table the query is based on.