Add Double Primary Key Using DAO

[Jake]

Hi,

I'm looking to add a double field primary key using DAO - the same thing as
highlighting 2 fields before clicking the PK button. The code I have is
below, and it keeps erroring out when I try to append the second index to the
TableDef. Please help!

Thanks!

'-----------------------Code Start---------------------------
Sub AppendKey()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim idxfld As DAO.Field

'code to add Sequence field
Set db = CurrentDb()
Set tdf = db.TableDefs("_tbl28h")

'Create a new primary key
Set idx = tdf.CreateIndex("PrimaryKey")
idx.Primary = True
idx.Required = True
idx.Unique = True

'Create the new index field
Set idxfld = idx.CreateField("AppealID")
idx.Fields.Append idxfld

'Append the index to the indexes collection
tdf.Indexes.Append idx

Set idx = Nothing
'Create a new primary key
Set idx = tdf.CreateIndex("PrimaryKey2")
idx.Primary = True
idx.Required = True
idx.Unique = True

'Create the new index field
Set idxfld = idx.CreateField("StatusType")
idx.Fields.Append idxfld

'Append the index to the indexes collection
tdf.Indexes.Append idx

'Refresh the indexes collection
tdf.Indexes.Refresh

Set idx = Nothing
Set tdf = Nothing
Set db = Nothing

End Sub

'--------------------------Code End---------------------------

 

[Douglas J. Steele]

Are you saying that both AppealID and StatusType should be in the primary
key?

Sub AppendKey()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim idxfld As DAO.Field

'code to add Sequence field
Set db = CurrentDb()
Set tdf = db.TableDefs("_tbl28h")

'Create a new primary key
Set idx = tdf.CreateIndex("PrimaryKey")
idx.Primary = True
idx.Required = True
idx.Unique = True

'Create the new index field
Set idxfld = idx.CreateField("AppealID")
idx.Fields.Append idxfld
Set idxfld = idx.CreateField("StatusType")
idx.Fields.Append idxfld

'Append the index to the indexes collection
tdf.Indexes.Append idx

'Refresh the indexes collection
tdf.Indexes.Refresh

Set idx = Nothing
Set tdf = Nothing
Set db = Nothing

End Sub

 

[Jake]

Perfect, thanks!

Are you saying that both AppealID and StatusType should be in the primary
key?

Sub AppendKey()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim idxfld As DAO.Field

'code to add Sequence field
Set db = CurrentDb()
Set tdf = db.TableDefs("_tbl28h")

'Create a new primary key
Set idx = tdf.CreateIndex("PrimaryKey")
idx.Primary = True
idx.Required = True
idx.Unique = True

'Create the new index field
Set idxfld = idx.CreateField("AppealID")
idx.Fields.Append idxfld
Set idxfld = idx.CreateField("StatusType")
idx.Fields.Append idxfld

'Append the index to the indexes collection
tdf.Indexes.Append idx

'Refresh the indexes collection
tdf.Indexes.Refresh

Set idx = Nothing
Set tdf = Nothing
Set db = Nothing

End Sub