Creating an Autonumber field programmatically

[Rob601]

I'm trying to assign an autonumber primary key and every resource I find uses the following approach to set the autonumber:

CurrentDb.TableDefs("myTable").Fields("RecID").Attributes = dbAutoIncrField

But I'm getting run-time error 3219 Invalid Operation. I have the DAO 3.6 object library activated.

The complete code is below. I'm replacing existing data in a table with data in a text file, and I want to delete the autonumber field and replace it so it will start again at zero when it repopulates.

Sub ReplacePK()

With CurrentDb
.Execute "DROP INDEX PrimaryKey ON myTable;"
.Execute "DROP INDEX RecID ON myTable;"
.Execute "ALTER TABLE myTable DROP COLUMN RecID;"
End With

CurrentDb.Execute "ALTER TABLE myTable ADD COLUMN RecID LONG;"
CurrentDb.TableDefs("myTable").Fields("RecID").Attributes = dbAutoIncrField

With CurrentDb
.Execute "CREATE INDEX PrimaryKey ON myTable (RecID) WITH PRIMARY;"
.Execute "CREATE INDEX RecID ON myTable (RecID);"
End With

End Sub

Any thoughts out there? Thanks!

 

[Rob601]

Okay, actually I figured it out. Since I'm using SQL anyway, the following method works:

With CurrentDb
.Execute "DROP INDEX PrimaryKey ON myTable;"
.Execute "DROP INDEX RecID ON myTable;"
.Execute "ALTER TABLE myTable DROP COLUMN RecID;"
.Execute "ALTER TABLE myTable ADD COLUMN RecID COUNTER;"
.Execute "CREATE INDEX PrimaryKey ON myTable (RecID) WITH PRIMARY;"
.Execute "CREATE INDEX RecID ON myTable (RecID);"
End With

So false alarm.