ADO: Primary Key on Multiple Columns

Environment:  VB6, ADO 2.1, Access 97

I want to programmatically create a Primary Key on several fields in an Access table using ADO/ADOX 2.1.  The code I'm working with:

Dim key1 As ADOX.Key
Dim col  As ADOX.Column
Dim tbl  As ADOX.Table
On Error Resume Next
For Each col In tbl.Columns
 Select Case (col.Name)
 Case "ID", "CODE"
  Set key1 = tbl.Keys("Key0")
  If (key1 Is Nothing) Then
     tbl.Keys.Append "Key0", adKeyPrimary, col.Name
     tbl.Keys("Key0").Columns.Append col
  End If
 End Select

This code results in only the field named "ID" being part of the Primary Key.  "CODE" should be a part of this key as well, but the request to add this column to the Primary Key column set is ignored for some reason.

Anyone have experience with this problem?  Thanks a lot.

Breck Ryker

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.

tbl.Keys("Key0").Columns.Append col.Name
And I wouldn't recommend to use
On Error Resume Next
I think
If tbl.Keys("Key0") Is Nothing
should work instead.
In this case you will be able to use the power of your debugger.
Hope it helps,

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
   tbl.Keys.Append "Key0", adKeyPrimary, col.Name
as the last step when creating a key. Once key is created, you cannot modify anything (except its Name)

Sample from ADO Help:

Sub CreateKey()
   Dim kyForeign As New ADOX.Key
   Dim cat As New ADOX.Catalog

   cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=c:\Program Files\Microsoft Office\" & _

   kyForeign.Name = "CustOrder"
   kyForeign.Type = adKeyForeign
   kyForeign.RelatedTable = "Customers"
   kyForeign.Columns.Append "CustomerId"
   kyForeign.Columns("CustomerId").RelatedColumn = "CustomerId"
   kyForeign.UpdateRule = adRICascade

   cat.Tables("Orders").Keys.Append kyForeign ' time consuming (indexing)
End Sub

To modify key, delete it, and rebuild again.
The other method is to execute a query which will be as follows:

ON Customer (CustomerId DESC, CustomerName) WITH PRIMARY
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ramaswamy_ravi changed the proposed answer to a comment
The other method is to execute a query:

ON Customer (CustomerId DESC, CustomerName) WITH PRIMARY

Execute this on the ADO Connection using ADODB.Recordset like any other statement. You also could create this as a query and execute this using the ADODB.Command
brykerAuthor Commented:
Appreciate the work-around, but genef proposed an earlier one that was just a slight modification to the method I had been trying to use.  Thanks again.
brykerAuthor Commented:
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
Visual Basic Classic

From novice to tech pro — start learning today.