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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 11:55 AM |
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 |