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:

----------------CODE_START
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
  Else
     tbl.Keys("Key0").Columns.Append col
  End If
 End Select
Next
----------------CODE_END

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
breck_ryker@mpsisys.com



brykerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
genefConnect With a Mentor Commented:
Try
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,
Gene
 
0
 
amebaCommented:
Use
   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\" & _
      "Office\Samples\Northwind.mdb;"

   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.
0
 
ramaswamy_raviCommented:
The other method is to execute a query which will be as follows:

CREATE UNIQUE INDEX PK_Customer
ON Customer (CustomerId DESC, CustomerName) WITH PRIMARY
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

CREATE UNIQUE INDEX PK_Customer
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
0
 
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.
0
 
brykerAuthor Commented:
Thanks.
0
All Courses

From novice to tech pro — start learning today.