Adding field using DAO createfield method

  • Thread starter Thread starter Mark A. Sam
  • Start date Start date
M

Mark A. Sam

Can someone demonstrate how to add a Autonumber field to an existing table
as a primary key using DAO?

Thanks.

Mark A. Sam
 
Modifying the example from the Help file, it would be something like:

Sub AddField()
Dim dbs As Database
Dim tdf As TableDef, fld1 As Field
Dim idx As Index, fldIndex As Field

' Return reference to current database.
Set dbs = CurrentDb
' Open the existing table.
Set tdf = dbs.TableDef("Contacts")
' Create new field in table.
Set fld1 = tdf.CreateField("ContactID", dbLong)
fld1.Attributes = fld1.Attributes + dbAutoIncrField
' Append field.
tdf.Fields.Append fld1
' Create primary key index.
Set idx = tdf.CreateIndex("PrimaryKey")
Set fldIndex = idx.CreateField("ContactID", dbLong)
' Append index fields.
idx.Fields.Append fldIndex
' Set Primary property.
idx.Primary = True
' Append index.
tdf.Indexes.Append idx
' Append TableDef object.
dbs.TableDefs.Append tdf
dbs.TableDefs.Refresh
Set dbs = Nothing
End Sub

If the table already has a Primary Key defined, you may have to delete the
existing index first, like

tdf.Indexes.Delete "PrimaryKey"
 
Mark said:
Can someone demonstrate how to add a Autonumber field to an existing table
as a primary key using DAO?
Click to expand...


I generally prefer to use a SQL DDL statement for this kind
of thing:

Dim db As Database
Set db = CurrentDb()
db.Execute "ALTER TABLE tablename" _
& "ADD COLUMN fieldname COUNTER " _
& "CONSTRAINT PrimaryKey PRIMARY KEY", _
dbFailOnError
Set db = Nothing
 
Back
Top