Set description on table fields in code

[MS$DesignersRCretins]

I am unable to set a table's Description property in code UNLESS it already exists! I have taken effort to build an an extremely easy-to-follow demonstration.

Create MyTable
Add 3 text fields
fld_1
fld_2
fld_3
Create one record with anything at all in it, or 1 record with nothing at all.

Create tblDescs
Add 2 text fields
MyField
MyDesc

Create 3 records with 2 values each with the 6 values below.
MyField MyDesc
fld_1 desc_1
fld_2 desc_2
fld_3 desc_3

Run this:

Sub AddFieldDescs_MyTable()
    Dim db As Database
    Dim rs As Recordset, rsDesc As Recordset
    Dim iRecordCount As Long
    
    iRecordCount = 0
    Set db = CurrentDb
    Set rs = db.OpenRecordset("MyTable")
    Set rsDesc = db.OpenRecordset("Descs")
  On Error GoTo err_DoneByeBye
    Do While True   'i.e., forever, until Exit Do or error; don't want to miss last record when EOF was hit
        rsDesc.Index = "MyField"
        rsDesc.Seek "=", rs.Fields(iRecordCount).Name
        rs.Fields(rsDesc("MyField")).Properties("Description") = rsDesc("MyDesc")
        'rs.Update
        iRecordCount = iRecordCount + 1
        If 0 = iRecordCount Mod 10000 Then Debug.Print iRecordCount & " records so far": DoEvents
    Loop
    Debug.Print iRecordCount & " records COMPLETED - TODOS - Hasta"
err_DoneByeBye: 'Don't go away mad;
    rsDesc.Close
    Set rsDesc = Nothing
     rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

It fails to add a description. Now open the tblDescs in Access in design view, and just type a space with the space bar in the description area by the field. Arrow down to seal it in, close window, save. Run the code again - and it WORKS.

1. Why does the code work if there's an existing description, else not?
2. If that's just the fact of life, how can I "seed" descriptions? I seem to be in an infinite loop. I can't set descriptions unless there already are descriptions!!

 

[jdraw]

Here is an older routine that partly does what you're talking about. It reports all table fields and descriptions.
It will build a table data_dictionary from scratch and populate it on each execution.

'---------------------------------------------------------------------------------------
' Procedure : DocumentTables
' DateTime  : 2006-06-09 09:29
' Author    : jack
' Purpose   : To create documentation for all tables in this database
'             where tableName <>MSys*  or TableName doesn't start with "~"
'
'**** Note: This routine builds a new table (data_dictionary) from scratch on each execution ****
'
'Fields that are recorded
'   table_name varchar(250)
'   table_description varchar(255)
'   field_name varchar(250)
'   field_description varchar(255)
'   ordinal_position NUMBER,
'   data_type varchar(15),"
'   length varchar(5)
'   default varchar(30)
'
'
' ' Last Modified:
'
' Inputs: N/A
' Dependency:   Calls Function FieldType
'---------------------------------------------------------------------------------------
'
Public Sub DocumentTables()
'Requires function FieldType

10  On Error GoTo Error_DocumentTables

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim strSQL_Drop As String
    Dim strSQL_Create As String
    Dim DDTableDesc As String

20  DDTableDesc = "Structure of Tables in this database"

    '==
    Dim idxLoop As Index


    '===
    'SQL to Delete existing copy of this table
30  strSQL_Drop = "DROP TABLE data_dictionary;"

    'SQL to Create the data_dictionary table
40  strSQL_Create = "CREATE TABLE data_dictionary" & _
                    "(EntryID AUTOINCREMENT PRIMARY KEY,table_name varchar(250) " & _
                    ",table_description varchar(255), field_name varchar(250) " & _
                    ",field_description varchar(255)," & _
                    "ordinal_position NUMBER, data_type varchar(15)," & _
                    "length varchar(5), default varchar(30));"

50  Set db = CurrentDb()
60  db.Execute strSQL_Drop, dbFailOnError
70  DoEvents
80  db.Execute strSQL_Create, dbFailOnError
90  DoEvents
100 Application.RefreshDatabaseWindow
    '
    '  add description property and value for data_dictionary table
    '
110 For Each tdf In db.TableDefs
120     If tdf.name = "Data_dictionary" Then
130         tdf.Properties.Append tdf.CreateProperty("Description", dbText, DDTableDesc)
140         Exit For
150     End If
160 Next tdf

170 DoEvents
180 Set rs = db.OpenRecordset("data_dictionary")

190 With rs
200     For Each tdf In db.TableDefs
210         If Left(tdf.name, 4) <> "Msys" _
               And Left(tdf.name, 1) <> "~" Then

220             For Each fld In tdf.Fields
230                 .AddNew
240                 !table_name = tdf.name
250                 !table_description = tdf.Properties("description")
260                 !Field_Name = fld.name
270                 !field_description = fld.Properties("description")
280                 !ordinal_position = fld.OrdinalPosition
290                 !data_type = FieldType(fld.Type)
300                 !Length = fld.Size
310                 !Default = fld.DefaultValue

320                 .Update
330             Next
340         End If
350     Next
360 End With

370 MsgBox "Tables have been documented", vbInformation, "TABLES DOCUMENTED"

380 rs.Close
390 db.Close

Exit_Error_DocumentTables:

400 Set tdf = Nothing
410 Set rs = Nothing
420 Set db = Nothing

430 Exit Sub

Error_DocumentTables:

440 Select Case Err.Number

    Case 3376

450     Resume Next    'Ignore error if table not found
460 Case 3270    'Property Not Found

470     Resume Next
480 Case Else

490     MsgBox Err.Number & ": " & Err.Description
500     Resume Exit_Error_DocumentTables

510 End Select

End Sub

The routine below is called by DocumentTables

'---------------------------------------------------------------------------------------
' Procedure : FieldType
' Author    : Jack
' Created   : 3/18/2008
' Purpose   : To identify fieldtypes in Access.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Private Function FieldType(v_fldtype As Integer) As String

10  On Error GoTo Error_FieldType

20  Select Case v_fldtype
    Case dbBoolean
30      FieldType = "Boolean"
40  Case dbByte
50      FieldType = "Byte"
60  Case dbInteger
70      FieldType = "Integer"
80  Case dbLong
90      FieldType = "Long"
100 Case dbCurrency
110     FieldType = "Currency"
120 Case dbSingle
130     FieldType = "Single"
140 Case dbDouble
150     FieldType = "Double"
160 Case dbDate
170     FieldType = "Date"
180 Case dbText
190     FieldType = "Text"
200 Case dbLongBinary
210     FieldType = "LongBinary"
220 Case dbMemo
230     FieldType = "Memo"
240 Case dbGUID
250     FieldType = "GUID"
260 End Select

Exit_Error_Fieldtype:
270 Exit Function

Error_FieldType:
280 MsgBox Err.Number & ": " & Err.Description
290 Resume Exit_Error_Fieldtype

End Function

NOTE:
For specific example of adding a description see http://allenbrowne.com/func-dao.html#SetFieldDescription

 

[MS$DesignersRCretins]

Thank you for answering but there was just too much there. After hours and hours and hours of trial and error I devised the following, which you did hint to. I'm not blaming you because for all you know I should have been able to interpret your code and got what I needed. The solution was ultimately just a few lines which I wish someone had simply supplied though.

This is now a useful routine. If an I.T. person gives you a set of field names and descriptions and you put in in a table such as tblDescs as I've described, the code will stick the descriptions on the actual data table that has those fields.

The critical issues:
- You seem to have to create the Description property on fields if their existing content is ""
If you manually go into table design view and type in anything, the property is "invisibly" created and given a value. If you hit the delete key on a value there, and go down arrow and save, there will BE NO description property for that field or fields. Not a property with an empty string or null; there will be NO property.
- You seem to HAVE to use a TableDef rather than Recordset to create a property.
What a hellish multi hour ordeal to discover this.
- You seem to have to go tdf.Fields(0), not tdf.Fields("fld_1"). Why? Why? Why did I have to spend hours to glean this super counterlogical "feature" ?
- Once you realize that you need CreateProperty, and you research that term, ignore 90% of the discussions which deal with using that on other objects than a TableDef. ONLY a TableDef works.
- Careful with OpenRecordset using a different type. The default type works in the code above. dbOpenDynaSet would seem to be more versatile, but it kills this code.
- Remember the basic silly rule that you don't work off of CurrentDB; you set db to it as below.

Sub AddFieldDescs_MyTable()
    Dim db As Database
    Dim rs As Recordset, rsDesc As Recordset
    Dim iRecordCount As Long
    Dim tdf As TableDef, i as long 'The key
    
    iRecordCount = 0
    Set db = CurrentDb
    Set tdf = db.TableDefs("mytable")
    
    '1st step: create description property on each field with "" in each
    On Error GoTo err_theyre_there_already
    For i = 0 To tdf.Fields.Count - 1
        tdf.Fields(i).Properties.Append tdf.CreateProperty("Description", dbText, "foo")
    Next i

err_theyre_there_already:
    Set rs = db.OpenRecordset("MyTable")
    Set rsDesc = db.OpenRecordset("Descs")
  On Error GoTo err_DoneByeBye
    Do While True   'i.e., forever, until Exit Do or error
        rsDesc.Index = "MyField"
        rsDesc.Seek "=", rs.Fields(iRecordCount).Name
        rs.Fields(rsDesc("MyField")).Properties("Description") = rsDesc("MyDesc")
        'rs.Update
        iRecordCount = iRecordCount + 1
        If 0 = iRecordCount Mod 10000 Then Debug.Print iRecordCount & " records so far": DoEvents
    Loop
    Debug.Print iRecordCount & " records COMPLETED - TODOS - Hasta"
err_DoneByeBye: 'Don't go away mad;
    rs.Close: rsDesc.Close        
    Set rs = Nothing: Set rsDesc = Nothing
    Set tdf = Nothing: Set db = Nothing
End Sub

I hope this prevents countless hours and lives lost on false trails on this matter.

 

[Solo712]

- You seem to have to go tdf.Fields(0), not tdf.Fields("fld_1"). Why? Why? Why did I have to spend hours to glean this super counterlogical "feature" ?

It is "counterlogical" only if you insist on refering to a field by name before you actually give it a name ! Why to spend hours on this discovery ? Well, let's just say that whoever or whatever is at fault here, it is not Access.

Best,
Jiri

 

[jdraw]

I agree with Jiri. You'd have to know the field names to use that syntax.
If you use 0 to .Count, you can get the names and properties --from which you could add/revise descriptions etc.
And I have to say that most developers have had to learn what you have discovered. There are far more youtubes etc now than there were years back. Many learned by experience (trial and error)--it's all part of Access, database or any other subject you can imagine.

Your countless hours is often equivalent to "several development projects over the years" by many. Nobody learns this stuff on first try.

Glad you have it working, and that you showed us the results of your effort.

 

[gemma-the-husky]

in general, some properties of fields "exist" others do not. if they do not, you have to create them first.

in some cases, you cannot even use this sort of thing,

msgbox "Name: " & prp.name & " Value: " & prp.value

because some properties do not even have a value, although you can error trap this.

If you need to do a lot of development, it is worth investing in a decent DAO manual to identify how you can use all these table, field, index, relationship objects.



basically you need a strucxture, and error handling to deal with this sort of pseudologic

 sub setdescription (tablename as string, fieldname as stirng, newdescription as string)
  
 if not exists tdf(tablename).fields(fieldname).property(description) then
     create the property
 end if
 tdf(tablename).fields(fieldname).property(description)  = newdescription

 

[MS$DesignersRCretins]

Thank you solo and jdraw and gamma for responding. I know that newbies sometimes scream "what is wrong with stupid Microsoft" so my harsh tone just begs for a "relax, it's all fine, you just need to learn" response, so your polite advice is accepted However right in the very first lines of O.P.:
Create MyTable
Add 3 text fields
fld_1
...

fld-1 does exist. Reading between the lines, are you implying is that the objects are not the actual Microsoft objects, but merely code objects? So the actual table has fld_1 but the code objects don't? That is, when I set a TableDef or Recordset to a table name, I am not operating on the table itself, but only on the object in code? I admit my Access object knowledge is cobbled and fractured, though I think you can tell from my routine above that I'm not on my maiden dance. For example observe the seasoned lack of useful comments :rofl: How about those hardwired strings, yuck yuck. And a brief confused limbo where I neglected to On Error GoTo 0 :banghead:

I will say this, having a tiny of of experience and trial and error based on one or two projects over four decades of development (from .ASM on up): once you get the hang of a product, such as knowing where to find something in the mentally deranged ribbons, that doesn't make it either logical or intuitive. It just means you've gotten used to it! After many many many many many many many projects with Borland, Quarterdeck, Microsoft, and others, I must state, MS designers are Cretins Massively.

That said, I seem to have a basic misconception on the transcendental aspects of Access objects, which might indeed be blameless here - GIVEN that fld_1 apparently doesn't exist yet. Looks like I need to start over with some DAO reading as suggested. Tip appreciated.

Thanks in particular for suggesting an improvement for
On Error GoTo err_theyre_there_already
which I'm not beamingly proud of, though I don't think it's just ridiculous.

 

[MS$DesignersRCretins]

Maybe the reason that you suggest fld_1 doesn't exist is that you all thought I was building a new table, rather than [what I interpreted as] setting a "pointer" to an existing one? 99% of web discussions and articles (yes, I put in a few hours researching and experimenting) seem to involve using those objects to create tables, not operate upon existing.

 

[gemma-the-husky]

MS. Perhaps this code block will help a little. This is all part of the DAO object model, which has a logical hierarchy of objects, among which are these.

database, tabledefs, fields, properties
there are other objects too, such as indexes, relationships, querydefs, and so on.

dim db as database
dim tdf as tabledef 'a single tabledef in the tabledefs collection (of a database)
dim fld as field ' a single field in the fields collection (of a table)
dim prp as property ' a single property in the properties collection (of a field)


set db = currentdb

set tdf = db.tabledefs("mytable")

'existing field - this will error if the field does not exist
set fld = tdf.fields("myfield")

'property
'some properties are not intrinsic, and this will error if the property indicated does not exist
set prp = fld.properties("description")
msgbox "Property Name: " & prp.Name

'hence alternatively, but this may error for some properties - as some properties do not have values
msgbox "Property Name: " & prp.Name & " Value: " & prp.value

'instead you can iterate the properties of a field
'again, this will error for properties that do not have values
for each prp in fld.properties
msgbox "Property Name: " & prp.name & " Value: " & prp.value
next


so, if the description property does not exist, we need to create it with this sort of code

[B]on error goto noproperty[/B]
[B]retest: [/B]' test it again!
[B]     description = fld.properties("description").value[/B]
[B]     msgbox "Field: " & fld.name & " Desc: " & description[/B]
[B]     exit sub[/B]

[B]noproperty:[/B]
     'include code to create the property "description" for the current field
[B]     resume retest[/B]
 [B]

[/B]

 

[MS$DesignersRCretins]

...
This is all part of the DAO object model, which has a logical hierarchy of objects,
...
set tdf = db.tabledefs("mytable")

'existing field - this will error if the field does not exist
set fld = tdf.fields("myfield")

Thank you for the suggestion. The statement does not error. At the same instant:

?tdf.Fields.Count
 3 
?tdf.Fields(0).Name
fld_1
?tdf.Fields("fld_1").Name
fld_1

And as noted in earlier code, tdf.Fields(0) does things that tdf.Fields("fld_1") does not (though not obvious from the Immediate window items just shown). I hope I can be forgiven for stating that this is counterlogical! And clearly the object does know "fld_1." I didn't tell the object that. All I did was create a table in the GUI and add the field in design mode, and ran this

Sub huh()
    Dim db As Database, tdf As TableDef, fld as Field
    Set db = CurrentDb
    Set tdf = db.TableDefs("mytable")
    
    'existing field - this will error if the field does not exist
    Set fld = tdf.Fields("fld_1")
End Sub

I don't mean to be a smart-aleck in saying that if there's a logical fallacy on my end, I'm still waiting to hear it. I may have reacted a little snappy at the mostly diplomatic suggestion that between me and Access the illogic is not in Access; but honestly, I want to make some sense of this simply contradictory-seeming behavior.

 

[gemma-the-husky]

I can't believe there is a difference between referring to an object by name, and by ordinal position.
What differences do you think you have observed?

 

[MS$DesignersRCretins]

Sorry, I thought that the original post was so simple and laid out step by step that this would take about 45 seconds for an object expert to solve, but I was mistaken, and this is devolving. So I hope no offense was taken by the loyal and unwavering MS-loving community; I retract any legitimate or illegitimate questioning herein of the MS design; and the revised code (revised on my second post) indeed sets descriptions on Access tables, and no one has said a word about the code choices therein: so I'm marking "Solved."

Thank you all for your advice and assistance with this code and its objective.

 

[MS$DesignersRCretins]

I can't believe there is a difference between referring to an object by name, and by ordinal position.
What differences do you think you have observed?

I covered that emphatically in the second thread post. Thanks for your question, but I'm marking the thread solved now and going on to other challenges. Thanks for your help!