How to populate field descriptions in MS Access

Question

When linking to an external data source via ODBC (especially an AS/400), I often run into cryptic field names on the other side, where a data dictionary is not available. In the rare event that I'm able to get the field descriptions from the other db, I would like to be able to import them all at once, rather than copy/paste each description into the table design form one at a time.

I wasn't able to find this in the system tables, so I don't know where this metadata is stored. Any ideas on where it is, and whether it can be updated in batch?

Update: I managed to read the schema using the OpenSchema method (see code below), but this returns a read-only dataset, making it impossible for me to update the descriptions.

Function UpdateFieldDescriptions()
Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As Recordset
Dim strSQL As String
Dim strDesc As String

Set cn = CurrentProject.Connection
Set rs = cn.OpenSchema(adSchemaColumns)

While Not rs.EOF
    If Left(rs!table_name, 4) <> "MSys" Then
        Debug.Print rs!table_name, rs!column_name, rs!Description
        strSQL = "SELECT Description " & _
            "FROM tblColumnDescriptions a " & _
            "WHERE a.Name = """ & rs!table_name & """ AND " & _
            "a.Column = """ & rs!column_name & """;"
        Set rs2 = CurrentDb.OpenRecordset(strSQL)
        While Not rs2.EOF
            strDesc = rs2.Fields(0)
            rs!Description = strDesc ' <---This generates an error
        Wend
    End If
    rs.MoveNext
Wend

rs.Update
rs.Close
Set rs = Nothing
Set rs2 = Nothing
Set cn = Nothing

End Function

Answer 1

Some notes that may help. The following shows field descriptions from adSchemaColumns.

   Function ListFieldDescriptions()
   ''List field descriptions
   Dim cn As New ADODB.Connection, cn2 As New ADODB.Connection
   Dim rs As ADODB.Recordset, rs2 As ADODB.Recordset

       Set cn = CurrentProject.Connection

       Set rs = cn.OpenSchema(adSchemaTables, _
           Array(Empty, Empty, Empty, "tablenamehere"))

       While Not rs.EOF
           Debug.Print rs!table_name; "   desc=  "; rs!Description
           Set rs2 = cn.OpenSchema(adSchemaColumns, _
               Array(Empty, Empty, "" & rs!table_name & ""))
           While Not rs2.EOF
               Debug.Print "     " & rs2!Column_Name
               Debug.Print "     " & rs2!Data_Type
               Debug.Print "     " & rs2!Description
               Debug.Print "     " & rs2!Is_Nullable
               rs2.MoveNext
           Wend
       rs.MoveNext
       Wend
       rs.Close
       Set cn = Nothing

   End Function

From: http://wiki.lessthandot.com/index.php/ADO_Schemas

Answer 2

Use DAO instead of ADO:

Sub SetFieldDesc(TblName As String, FldName As String, Description As String)
Dim db As DAO.Database, td As DAO.TableDef, fld As DAO.Field

    Set db = CurrentDb()
    Set td = db.TableDefs(TblName)
    Set fld = td.Fields(FldName)

    On Error Resume Next
    fld.Properties("Description") = Description
    If Err.Number = 3270 Then 'Property not found.'
        fld.Properties.Append fld.CreateProperty("Description", dbText, Description)
    End If
End Sub