I have created code that reads the tables in my project and creates tables that contain information about the table structure.

It uses the following method to loop thru the objects...
    For Each tbl in db.TableDefs
         For Each fld In tbl.Fields

Then it uses the VarType function to return the integer indicating the subtype of each field.

The problem I have is that if the field has not been initialized yet (the table is empty) then it returns all the fields as type 9 (object), even though the field is specified as say a 'Long' in the table design.

Tried the TypeName function also and have a different problem if they are uninitialized.

How can I get the definitions of the types of fields in tables when the table is still empty??

Thankyou

Never mind I think I found something that I can use.....

Field Object, Fields Collection Example

This example shows what properties are valid for a Field object depending on where the Field resides (for example, the Fields collection of a TableDef, the Fields collection of a QueryDef, and so forth). The FieldOutput procedure is required for this procedure to run.

Sub FieldX()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset
    Dim fldTableDef As Field
    Dim fldQueryDef As Field
    Dim fldRecordset As Field
    Dim fldRelation As Field
    Dim fldIndex As Field
    Dim prpLoop As Property

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees")

    ' Assign a Field object from different Fields
    ' collections to object variables.
    Set fldTableDef = _
        dbsNorthwind.TableDefs(0).Fields(0)
    Set fldQueryDef =dbsNorthwind.QueryDefs(0).Fields(0)
    Set fldRecordset = rstEmployees.Fields(0)
    Set fldRelation =dbsNorthwind.Relations(0).Fields(0)
    Set fldIndex = _
        dbsNorthwind.TableDefs(0).Indexes(0).Fields(0)

    ' Print report.
    FieldOutput "TableDef", fldTableDef
    FieldOutput "QueryDef", fldQueryDef
    FieldOutput "Recordset", fldRecordset
    FieldOutput "Relation", fldRelation
    FieldOutput "Index", fldIndex

    rstEmployees.Close
    dbsNorthwind.Close

End Sub

Sub FieldOutput(strTemp As String, fldTemp As Field)
    ' Report function for FieldX.

    Dim prpLoop As Property

    Debug.Print "Valid Field properties in " & strTemp

    ' Enumerate Properties collection of passed Field
    ' object.
    For Each prpLoop In fldTemp.Properties
        ' Some properties are invalid in certain
        ' contexts (the Value property in the Fields
        ' collection of a TableDef for example). Any
        ' attempt to use an invalid property will
        ' trigger an error.
        On Error Resume Next
        Debug.Print " " & prpLoop.Name & " = " & _
            prpLoop.Value
        On Error GoTo 0
    Next prpLoop

End Sub