SOLVED

Tabledef Indexes Append

New Contributor

Procedure compiles correctly but gives the following error:

Run time error 3409

Invalid field definition "PrimaryIndex" in definition of index.

Instruction flagged:

tdfMenu.Indexes.Append idxPrimary

 

What am I doing wrong?

 

Public Sub CreateTable()

Const strTableName As String = "NewTable"
Dim dbMe As Database
Dim tdfMenu As TableDef
Dim idxPrimary As Index

Set dbMe = CurrentDb
Set tdfMenu = dbMe.CreateTableDef(strTableName)

With tdfMenu
.Fields.Append .CreateField("key", dbLong)
.Fields.Append .CreateField("Option", dbText)
.Fields("key").Required = True
.Fields("key").AllowZeroLength = False
.Fields("key").Attributes = dbAutoIncrField
.Fields("Option").Required = True
.Fields("Option").AllowZeroLength = False
End With

dbMe.TableDefs.Append tdfMenu
Set idxPrimary = tdfMenu.CreateIndex("PrimaryIndex")

With idxPrimary
.Fields.Append idxPrimary.CreateField("PrimaryIndex")
.Primary = True
End With

tdfMenu.Indexes.Append idxPrimary

End Sub

2 Replies
best response confirmed by RoyLehmann (New Contributor)
Solution
you don't have "PrimaryIndex" field name when you created the Table.
you select from the table field which one is the Field you are Indexing:
to make "key" field the Primary Index:

...
...
With idxPrimary
.Fields.Append idxPrimary.CreateField("key")
.Primary = True
End With

tdfMenu.Indexes.Append idxPrimary
Thank you for your help. Problem solved.