Code Primary Key Problem

[john]

Here is my code, Table is tblCurrentV and Field name is ID, that I need to
make as Primary after it is created by Make-Table Query. I don't get an
error, but when I run the code , but when I look at the Table after it runs,
it is still not set to Primary Key. I need it so another query that is
related will be updateable.

Dim db As Database
Dim idx As index
Dim tdf As TableDef

Set db = CurrentDb
Set tdf = db.TableDefs("tblCurrentV")
With tdf
Set idx = .CreateIndex("ID")
With idx
.Primary = True
End With
End With

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

***********
I've also tied this code, get an error saying it cannot find the table?

db.Execute "CREATEINDEX PrimaryKey ON tblCurrentV (ID);"

Thanks John

 

[Allen Browne]

Your code also needs to
- Append the field to the Fields collection of the index.
- Append the new index to the Indexes collection of the table.

Try this:

Set idx = tdf.CreateIndex("PrimaryKey")
With idx
.Fields.Append .CreateField("ID")
.Primary = True
End With
tdf.Indexes.Append ind

Note: The primary key index is usually named "PrimaryKey". That's not
essential: just for clarify and consistency.

 

[GraemeR]

Hi John, two problems with the code. See comments in this working example

Public Sub testindex()
' Declarations added for completeness.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index

Set db = CurrentDb()
Set tdf = db.TableDefs("tblCurrentV")

With tdf
Set idx = .CreateIndex("ID")

With idx
' Problem 1: Need to add some fields to the index
.Fields.Append .CreateField("ID")

.Primary = True
End With
End With

' Problem 2: Need to add the index to the table
tdf.Indexes.Append idx

Set db = Nothing
Set tdf = Nothing
Set idx = Nothing
End Sub

HTH, Graeme.