Create Composite Primary Key in VBA (DAO) 1

[teach314]

I have a table (tblResults) in an existing db like shown below. ITEM is the PRIMARY KEY.

ITEM    Col1   Col2   Col3 ... etc  
1000     23     466    11  
1024     22      18   299
1025    371     772    12
etc....

Now I need a new first column, CLASS, and I need the PRIMARY KEY to be COMPOSITE (CLASS / ITEM) .

I've modified code from Allen Browne to create the new field, CLASS.

Function ModifyTableDAO()
    'Purpose:   How to add and delete fields to existing tables.
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    'Initialize
    Set db = CurrentDb()

    Set tdf = db.TableDefs("tblResults")
    
    'Add a field to the table.
    tdf.Fields.Append tdf.CreateField("CLASS", dbInteger)

    'Clean up
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function

Once the field is created, I use an UPDATE query set each value of CLASS to "A".

Here's where I'm stuck. I now need to set the fields CLASS / ITEM as a COMPOUND PRIMARY KEY. (I'm preparing, here, to be able to have new tables that have CLASS = B or C while having the same ITEM values) Can anyone suggest code to properly set this COMPOSITE PRIMARY KEY?

many thanks!
Teach 314

 

[madonnac]

create your index object, then append the fields to it. by setting the index as primary, you unset the other index from being primary.

Example code:

Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field

...


Set idx = tdf.CreateIndex("FirstKey")
Set fld = idx.CreateField("ITEM")
idx.Fields.Append fld 'add the fields to the index
Set fld = idx.CreateField("Class")
idx.Fields.Append fld ' and the second field

With idx
    .Unique = True
    .Primary = True 'This removes any other primary field
End With

With tdf
    .Indexes.Append idx 'add the index
    .Indexes.Refresh 'and update the table
End With

 

[teach314]

Got it! Thanks, madonnac.