I am trying to use VBA code to create a primary key index in an existing database. I am trying this code, but when it gets to the line tbl.Indexes.Append idx I always get the error: Invalid Field Definition idx1 in definition of index or relationship. What am I doing wrong?

Private Sub Addkey_Click()
On Error Go to ErrHandler

Dim db As Database
Dim tbl As TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index

Tablen = Me.tablenm

Set db = CurrentDb()
Set tbl = db.TableDefs(Tablen)
Set idx = tbl.CreateIndex("Idx1")

Set fld = idx.CreateField("idx1")
idx.Fields.Append fld
idx.Primary = True
tbl.Indexes.Append idx

CleanUp:

Set fld = Nothing
Set idx = Nothing
Set tbl = Nothing
Set db = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in Addkey( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
Go to CleanUp
End Sub

A Primary key is automatically indexed. You don't need to add an index if you set the field as a PK. Try commenting out the offending line and then check the table to see if the field has been designated as a PK.

Quote:

---

Originally Posted by ScottGem

A Primary key is automatically indexed. You don't need to add an index if you set the field as a PK. Try commenting out the offending line and then check the table to see if the field has been designated as a PK.

---

Unfortunately, when I comment out tbl.Indexes.Append idx nothing happens to the database.