A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to programmatically edit table and column comment fields



 
 
Thread Tools Display Modes
  #1  
Old August 31st, 2005, 08:50 PM
Siegfried Heintze
external usenet poster
 
Posts: n/a
Default How to programmatically edit table and column comment fields

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


  #2  
Old September 1st, 2005, 02:45 AM
Allen Browne
external usenet poster
 
Posts: n/a
Default

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Siegfried Heintze" wrote in message
...
How do I write a program to CRUD (create, read, update & delete) comment
fields in columns, tables and other entities?
Thanks



  #3  
Old September 4th, 2005, 11:27 PM
Siegfried Heintze
external usenet poster
 
Posts: n/a
Default

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" wrote in message
...
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Siegfried Heintze" wrote in message
...
How do I write a program to CRUD (create, read, update & delete) comment
fields in columns, tables and other entities?
Thanks





  #4  
Old September 5th, 2005, 02:45 AM
Allen Browne
external usenet poster
 
Posts: n/a
Default

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Siegfried Heintze" wrote in message
...
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" wrote in message
...
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" wrote in message
...
How do I write a program to CRUD (create, read, update & delete)
comment
fields in columns, tables and other entities?
Thanks



  #5  
Old September 7th, 2005, 06:06 AM
Siegfried Heintze
external usenet poster
 
Posts: n/a
Default

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


  #6  
Old September 7th, 2005, 08:22 AM
Allen Browne
external usenet poster
 
Posts: n/a
Default

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Siegfried Heintze" wrote in message
...
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



  #7  
Old September 7th, 2005, 03:40 PM
Siegfried Heintze
external usenet poster
 
Posts: n/a
Default

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.ToStri ng())
Dim xmlAttr7 As XmlAttribute =
xmlTag.OwnerDocument.CreateAttribute("type")
xmlAttr7.Value = oTable.Properties(ii).Type.ToString()
xmlProp.Attributes.Append(xmlAttr7)
Next


  #8  
Old September 7th, 2005, 03:46 PM
Allen Browne
external usenet poster
 
Posts: n/a
Default

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("De scription")

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Siegfried Heintze" wrote in message
...
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.ToStri ng())
Dim xmlAttr7 As XmlAttribute =
xmlTag.OwnerDocument.CreateAttribute("type")
xmlAttr7.Value = oTable.Properties(ii).Type.ToString()
xmlProp.Attributes.Append(xmlAttr7)
Next



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Toolbars, Drop-Down Menus Rick New Users 1 September 21st, 2005 11:17 AM
Lookup Table Dilemma Karen Worksheet Functions 2 June 10th, 2005 08:22 PM
Table Design A. Williams Database Design 3 April 29th, 2005 07:02 PM
unable to repair inobox Sudheer Mumbai General Discussion 1 February 20th, 2005 12:55 PM
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. HDW Database Design 3 October 16th, 2004 03:42 AM


All times are GMT +1. The time now is 06:46 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.