How to determine column data type

[vamoose]

I have a Access 2000 database. In this table are 10 columns. I need to determine what the column named Auto has for a data type. If this comes back as an AutoNumber column then I need to drop it and add a new one. If not AutoNumber then all is well. Thank you very much.

 

[Remou]

Here are a few notes:

Dim db As Database
Dim tdf As TableDef
Dim fld As Field

Set db = CurrentDb
Set tdf = db.TableDefs("tblTable")

'Is Auto an AutoIncrement field?
If (tdf.Fields("Auto").Attributes And dbAutoIncrField) = dbAutoIncrField Then
    'You cannot delete a field that is used in an index.
    'Let us assume that Auto is used as a PrimaryKey
    tdf.Indexes.Delete "PrimaryKey"
    
    'Delete field
    tdf.Fields.Delete "Auto"
    tdf.Fields.Refresh
    
    'New AutoIncrement
    Set fld = tdf.CreateField("NewAuto", dbLong)
    fld.Attributes = fld.Attributes Or dbAutoIncrField

    With tdf.Fields
        .Append fld
        .Refresh
    End With

End If

I am curious as to why, though.

 

[vamoose]

Remou, your Dim statements don't seem to be recognized by Access. Error: User-defined type not defined. Any ideas. Thank you.

If the column type is AutoNumber then the file is the original database file. If it is not AutoNumber then the database has been edited and is not the original database file.

 

[MajP]

You need a reference to DAO

Go to "tools", "references", and select "Microsoft DAO 3.X Object

 

[Remou]

It is also best add the DAO as well, remember, I said notes
[tt]Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field[/tt]