Insert an 'ID' autonumber primary key field

[sumdumgai]

Searched and searched but can't find an answer. How can I insert an 'ID' field into an existing table at first field as primary key using AutoNumber? The table will then be populated.
Thanks.

 

[vbaInet]

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("[COLOR="Blue"]TableName[/COLOR]")
    Set fld = tdf.CreateField()
    
    With fld
        .Name = "AutoID"
        .Type = dbLong
        .Attributes = dbAutoIncrField
    End With
    
    tdf.Fields.Append fld
    tdf.Fields.Refresh
    
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing

 

[sumdumgai]

Thanks again. Your code adds field okay, but is there a way to:
1) Insert it as the first field
2) Make it the primary key as Access does when you import data and allow Access to create the primary key (ID) for you?

 

[vbaInet]

Whatever field you add first becomes the first one, if you want to change it's position there's a property called OrdinalPosition. Play around with the code and you'll see the different properties.

Hint: Adding an index is similar to adding a field. Look it up.

 

[sumdumgai]

Thanks,

Added ".OrdinalPosition = 0" to your code but the field is inserted at column 2; i.e., after the first field. Design view shows same thing. I've tried it several times with 'refresh' before and after the field create.

 

[vbaInet]

I suppose you change the ordinal position after adding it to the table.

 

[sumdumgai]

Tried with this:
With fld
.Name = "ID"
.Type = dbLong
.Attributes = dbAutoIncrField
End With

tdf.Fields.Append fld
tdf.Fields.Refresh

With fld
.OrdinalPosition = 0
End With

tdf.Fields.Refresh

Always goes to second field. I know this is only an aesthetic thing but I'd like to insert it at first field.

 

[vbaInet]

The fld object is a lone object, it's not part of the tabledef (tdf). It's only the definition, properties and attributes of fld that gets added to tdf when you call Append, not the fld object itself.

You need to refer to the tabledef itself:

tdf.Fields(fld.name) = 0

 

[sumdumgai]

Have most of it done, using VBA:
1) linked to external text file
2) copied linked table header (structure only) to local table, selecting only fields in linked table that are required (used an array to specify field positions wanted)
3) added 'ID' field at OP=0
4) created indices for four fields, including primary

Now, using same array technique, I'll build an SQL statement with VBA that will populate the local table with all of the data in the linked table.

Your help is much appreciated, along with all the others that provided sample code.
Thanks.

 

[vbaInet]

Good to hear!

Happy coding!