bryker
asked on
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.A
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
CREATE UNIQUE INDEX PK_Customer
ON Customer (CustomerId DESC, CustomerName) WITH PRIMARY
ramaswamy_ravi changed the proposed answer to a comment
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
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
ASKER
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.
ASKER
Thanks.
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.OL
"Data Source=c:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.
kyForeign.Name = "CustOrder"
kyForeign.Type = adKeyForeign
kyForeign.RelatedTable = "Customers"
kyForeign.Columns.Append "CustomerId"
kyForeign.Columns("Custome
kyForeign.UpdateRule = adRICascade
cat.Tables("Orders").Keys.
End Sub
To modify key, delete it, and rebuild again.