listing all tables / fields

[stuartam]

hi guys, i am trying to list all the tables in my database and then all the fields within each one, which i have managed to do with the code below:

Dim db As Database
Set db = DBEngine.Workspaces(0).Databases(0)
For i = 0 To db.TableDefs.Count - 1
    If db.TableDefs(i).Connect <> "" Then
        Debug.Print db.TableDefs(i).name
        For x = 0 To db.TableDefs(i).Fields.Count - 1
            Debug.Print "      " & db.TableDefs(i).Fields(x).name & " - " & db.TableDefs(i).Fields(x).Type
        Next
    End If
Next

but how do i add the field type at the moment it shows a number which represents the data type ( ie, number / text / etc ) and i want it to show, 'autonumber' / 'text' / 'date' etc and i would also like to display the comment relating to each field.

any ideas?

 

[Vbark]

Type is in Filed.Type and Autonumber is in attributes, each number relates to a type that can be looked up in access help.


this returns what you want.

Public Sub EmunerateTables()
    Dim tdf                 As DAO.TableDef
    Dim fld                 As DAO.Field
    Dim TypeString          As String
    Dim i                   As Integer
    
For Each tdf In DBEngine.Workspaces(0).Databases(0).TableDefs
    Debug.Print "Table: " & tdf.Name
    For Each fld In tdf.Fields
        Select Case fld.Type
            Case dbBigInt
            TypeString = "BigInt"
            Case dbBinary
            TypeString = "Binary"
            Case dbBoolean
            TypeString = "Boolean"
            Case dbByte
            TypeString = "Byte"
            Case dbChar
            TypeString = "Char"
            Case dbCurrency
            TypeString = "Currency"
            Case dbDate
            TypeString = "Date"
            Case dbDecimal
            TypeString = "Decimal"
            Case dbDouble
            TypeString = "Double"
            Case dbFloat
            TypeString = "Float"
            Case dbGUID
            TypeString = "GUID"
            Case dbInteger
            TypeString = "Integer"
            Case dbLong
            TypeString = "Long"
            Case dbLongBinary
            TypeString = "LongBinary"
            Case dbMemo
            TypeString = "Memo"
            Case dbNumeric
            TypeString = "Numeric"
            Case dbSingle
            TypeString = "Single"
            Case dbText
            TypeString = "Text"
            Case dbTime
            TypeString = "Time"
            Case dbTimeStamp
            TypeString = "TimeStamp"
            Case dbVarBinary
            TypeString = "VarBinary"
            End Select
        
        Debug.Print "   Field: " & fld.Name & ", Type: " & TypeString & IIf(fld.Attributes = 17, " AutoNumber", "")
        Next fld
    
    Next tdf

End Sub

 

[stuartam]

thanks il give it a go