set up primary key via code

[Phil]

Hi,

I imported a FoxPro table into Access 97 but there is no
primary key for the table. I would like to use one of the
existing fields as a primary key. I manually go to the
table design view and set the primary key. I am wondering
if it is possible to set it via code. Thank you so much.

 

[Allen Browne]

Use CreateIndex() on the TableDef.
Append the field(s) to the index's Fields collection, and set its Primary
property.
Append the index to the Indexes collection of the TableDef.

Example:

Sub CreateIndexDAO()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index

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

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

Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

 

[Phil]

Thank you Allen.

-----Original Message-----
Use CreateIndex() on the TableDef.
Append the field(s) to the index's Fields collection, and set its Primary
property.
Append the index to the Indexes collection of the TableDef.

Example:

Sub CreateIndexDAO()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index

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

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

Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

much.


.