Field Description ---> VBA read/set ??

[deedub84]

Is there any way to programatically read or set the field description in the database design view?

Thanks,
DW

[RobDog888]

We are talking about Access I assume.

You can read the property Description like so...

'Read:
MsgBox Application.CurrentDb.TableDefs("Table1").Fields(0).Properties("Description").Value = "Testing description props of first field."
 
'Write:
Application.CurrentDb.TableDefs("Table1").Fields(0).Properties("Description").Value = "Testing field description property write"

[deedub84]

Yes, Access.

I have a procedure that fills a table with a list of the tables, and fields in the table to produce a report that summarizes the database (see below). I am running into a <property not found> error for those fields where I haven't filled in a description.

I can't seem to trap and skip the error using an If ... Then. I've tried testing if it is Null and also "". Any suggestions?

Dim db As Database
Dim AllTableDefs As DAO.TableDefs
Dim AllFields As DAO.Fields
Dim rstTableList As DAO.Recordset
Dim qdfTables As DAO.QueryDef
Dim NumRecsTableList As Long
Dim NumFields As Long
Dim qdfDelete As DAO.QueryDef
 
Dim TCount As Long
Dim strSQLDelete As String
 
 
Set db = CurrentDb()
 
' Set AllTableDefs to definitions of all tables in the database:
Set AllTableDefs = db.TableDefs
Set qdfTables = db.QueryDefs("qTableList")
Set rstTableList = db.OpenRecordset("Tablelist", dbOpenDynaset)
rstTableList.MoveLast
 
NumRecsTableList = rstTableList.RecordCount
 
'reset the TableList
 
If NumRecsTableList > 0 Then
    strSQLDelete = "DELETE * from [TableList]"
        
    Set qdfDelete = db.CreateQueryDef("", _
            strSQLDelete)
    
    qdfDelete.Execute
End If
        
' Display names of all tables in database:
For j = 0 To AllTableDefs.Count - 1
If Left(AllTableDefs(j).Name, 1) <> "~" And Left(AllTableDefs(j).Name, 4) <> "MSys" And Left(AllTableDefs(j).Name, 1) <> "_" Then
Set AllFields = AllTableDefs(j).Fields
    For k = 0 To AllFields.Count - 1
        
    With rstTableList
        .AddNew
        !tablename = AllTableDefs(j).Name
        !FieldName = AllFields(k).Name
        If AllFields(k).Properties("Description").Value = "" Then
        Else
            Debug.Print AllFields(k).Properties("Description").Value
            !Description = AllFields(k).Properties("Description").Value
        End If
        !TYPE = FieldType(AllFields(k).TYPE)
        !length = AllFields(k).Size
        !DefaultValue = AllFields(k).DefaultValue
        !Position = AllFields(k).OrdinalPosition
        .Update
    End With
Next
End If
Next
 
 
End Sub

Thanks!