Set Primary Key with VBA

[gumby]

Is thier anyway to set the primary Key of a table with VBA? I have a
make table query that runs, however when it creats the table it does
not set a primary key. I would like to do this with VBA instead of
manually.

Thanks,
David

 

[gumby]

Is thier anyway to set the primary Key of a table with VBA? I have a
make table query that runs, however when it creats the table it does
not set a primary key. I would like to do this with VBA instead of
manually.

Thanks,
David

I figured it out.

Public Sub createPKIndex()

On Error GoTo ErrHandler

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

Set db = CurrentDb()
Set tbl = db.TableDefs("TableName")
Set idx = tbl.CreateIndex("PrimaryKey")

Set fld = idx.CreateField("FieldName")
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 createPKIndex( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub