How to create a multi-field primary key in Access using DAO in VBA?

I have a task in Access 2016 where I'm required to create a primary key consisting of multiple fields.  I need to do it in VBA code using the DAO model.  This is because it is a repetitive task and I need to loop through multiple tables setting a primary key each time the tables are replaced.  From what I can gather, what is needed are multiple fields to be added to the indexes collection and the primary key properties are set before the index is appended to the collection.  I would really prefer to receive some example code using VBA and DAO in Access so that I can work with the example and adapt it to my situation.  Thank you.
If Helen Feddema is available, that would be good.
Dave GoodmansonData AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
You need to use the Constraint clause of Alter Table

https://msdn.microsoft.com/en-us/library/bb177883(v=office.12).aspx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Also as an alternative,  you could possibly create the Primary Key as an Auto Number, then create a Unique Index on the required fields.
Gustav BrockCIOCommented:
You do it the usual way, just adding both fields to the index:

Set ThisTable = <your Table>

' Create a new Index object.
Set NewIndex = ThisTable.CreateIndex("NameOfIndex") 
NewIndex.Primary = False
NewIndex.Unique = False
NewIndex.IgnoreNulls = False

' Add fields.
Set ThisField = NewIndex.CreateField("NameOfFirstField")  
NewIndex.Fields.Append ThisField
Set ThisField = NewIndex.CreateField("NameOfSecondField")  
NewIndex.Fields.Append ThisField

' Append the compound index.
ThisTable.Indexes.Append NewIndex

Open in new window

/gustav
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Download eBook
Armen Stein - Microsoft Access MVP since 2006President, J Street TechnologyCommented:
I second Joe's advice - I really don't recommend composite primary keys.  They make everything harder down the line - child records, grandchild records, many-to-many relationships, etc.  Much better to use meaningless single primary keys (AutoNumber) and control duplicates with unique indexes.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave GoodmansonData AnalystAuthor Commented:
I want to thank those who responded.  I appreciate Gustav's pseudo-code, that has helped my understanding of this issue.  The responses from Joe and Armen are relevant but in this situation I am using these keys on a temporary basis.  Once they have done their job on the temp tables they are set up on, I can delete them at the end.  But it is handy to know the code to create them quickly.
Gustav BrockCIOCommented:
Pseudo-code? Please - I provided the exact answer to your question.

/gustav
PatHartmanCommented:
Since you apparently didn't understand my link, here's further explanation with the link obfuscated so hopefully, you'll get to see it before it gets deleted.

http  ://  www     dot      orafaq      dot     com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands

I never write code when I have the option of using a query so my choice for modifying the schema would be DDL as described in the original link.  You run the DDL using .Execute
Dave GoodmansonData AnalystAuthor Commented:
I obviously hadn't realised what a delicate group some of the Experts can be!  If I didn't understand something it is probably why I'm posting here in the first place.  Ask an expert.  Well I reconstructed your link and went to the oracle faq site.  OK, that's fine, so it has comments about various types of data language.  That's great, you have an opinion, I didn't find that it had a lot to do with my question.  But my question has been answered, and I've accepted the solution, so that's it.  Thank you!  Next question.
PatHartmanCommented:
The first link explains how to do what you asked to do by using a query rather than writing code.
Here is the relevant example

This example creates a new table called MyTable with two text fields, a Date/Time field, and a unique index made up of all three fields.
Sub CreateTableX2()

    Dim dbs As Database

    ' Modify this line to include the path to Northwind
    ' on your computer.

    Set dbs = OpenDatabase("Northwind.mdb")

    ' Create a table with three fields and a unique
    ' index made up of all three fields.
    dbs.Execute "CREATE TABLE MyTable " _
        & "(FirstName CHAR, LastName CHAR, " _
        & "DateOfBirth DATETIME, " _
        & "CONSTRAINT MyTableConstraint UNIQUE " _
        & "(FirstName, LastName, DateOfBirth));"

    dbs.Close

End Sub
 
The second link (obfuscated) was to a site that explained what DDL, DML, DCL, and TCL are since not many people get beyond DML.
Dave GoodmansonData AnalystAuthor Commented:
That's great, thanks very much Pat.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.