ms access table showing table structure

[edfromnz]

I want to generate a table with one record for each field in the tables in my database. Columns would be table name, field name, field type, field description.
I can extract field properties as numeric values, but cannot easioly translate these into meaningful text. I cannot get the field description.
How can I make sense of the field properties programmatically?

[Stewart Ross]

Hi, and welcome to Bytes.

The sample code below may give some ideas about accessing table definitions and the fields collection for each table in code. The name property of a field is a string, but its type property is a numeric value mapped to internal VB constants representing each data type (use the help file for the Fields collection to look up the mapping). To map the numeric field type values to their corresponding descriptions you will need to generate the mappings manually, either in code or by a mapping table. For example, the value of the VB constant dbText is 10 and that of dbMemo is 12, so a field of type 10 is a text field (whose size property defines its length) and type 12 a memo field.

I have not found a description property in the fields collection so far.

-Stewart

Public Function TableData()
    Dim oTableDef As TableDef
    Dim oField As DAO.Field
    For Each oTableDef In CurrentDb.TableDefs
        With oTableDef
            Debug.Print "<" & .Name & ">"
            For Each oField In .Fields
                With oField
                    Debug.Print .Name, .Type, .Size
                End With
            Next oField
        End With
    Next oTableDef
End Function