How do you create joint PKs using VBA?

[David Anderson]

I am using the following VBA code in Access 2003 to create a Primary Key in
a table exported from SQL Server (the export process does not preserve the
PKs). However, I have to do something similar for tables with two or more
fields that act as a joint PK. For example, I have an EntrySummary table
with a joint PK of EntrantID and CompID.

Does anyone know how I can use VBA to create a joint PK?

David

===========================================
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index

Set db = CurrentDb()

Set tdf = db.TableDefs("Users")

For Each ind In tdf.Indexes
If ind.Name = "PrimaryKey" Then
'Exit if PK already exists
Exit Sub
End If
Next ind

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

Set ind = Nothing
Set tdf = Nothing
Set db = Nothing

 

[Douglas J. Steele]

Chris has shown you one way, but if you want to continue using DAO, it would
be something like:

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

 

[David Anderson]

Hi Douglas,
I do indeed with to continue using DAO. That was exactly the code I had
already tried, but it had no effect. Neither EntrantID nor CompID end up as
PKs.

David

 

[David Anderson]

Douglas,
My stupid mistake. The code in question was being totally bypassed due to an
incorrect GoTo statement!! It is now working correctly and the joint PK has
been created as required.

David