INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Create Composite Primary Key in VBA (DAO)

Create Composite Primary Key in VBA (DAO)

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

CODE

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.

CODE

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

RE: Create Composite Primary Key in VBA (DAO)

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:

CODE --> vba

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 

RE: Create Composite Primary Key in VBA (DAO)

(OP)
Got it! Thanks, madonnac.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close