Adding a column to an existing ADOX Catalog's table's key

Does anyone know how (or whether it is possible) to add a column to an
existing key for a column using ADOX?

pseudo code:
Dim cn as ADODB.Connection
Dim lCat as new ADOX.Catalog
Dim ltbl as new ADOX.Table
Dim lKey as New ADOX.Key

'open the cn

Set lCat.ActiveConnection = cn
Set ltbl = lCat.Table("Table1")
Set lKey = ltbl.Key("Primary_Key")

'At this point, if there is one column as the primary key for Table1 then
'lKey.Columns.Count will = 1
lKey.Columns.Append "SomeField"

'At this point, the lKey.Columns.Count will still = 1

If you add a new key, you can add as many columns as you want and the count
will incriment as you do so.
So, do I have to delete the key and readd it or is there another way to do
this through ADOX?
Thanks,
CF

On Thu, 17 Feb 2005 16:56:41 -0500, "Wart" <nospamWart RemoveThis @epix.net> wrote:

¤ Does anyone know how (or whether it is possible) to add a column to an
¤ existing key for a column using ADOX?
¤
¤ pseudo code:
¤ Dim cn as ADODB.Connection
¤ Dim lCat as new ADOX.Catalog
¤ Dim ltbl as new ADOX.Table
¤ Dim lKey as New ADOX.Key
¤
¤ 'open the cn
¤
¤ Set lCat.ActiveConnection = cn
¤ Set ltbl = lCat.Table("Table1")
¤ Set lKey = ltbl.Key("Primary_Key")
¤
¤ 'At this point, if there is one column as the primary key for Table1 then
¤ 'lKey.Columns.Count will = 1
¤ lKey.Columns.Append "SomeField"
¤
¤ 'At this point, the lKey.Columns.Count will still = 1
¤
¤ If you add a new key, you can add as many columns as you want and the count
¤ will incriment as you do so.
¤ So, do I have to delete the key and readd it or is there another way to do
¤ this through ADOX?


AFAIK, you have to drop (delete) the existing primary key and create a new one. I'm not aware of any
way to *modify* an existing primary key through ADOX, since you add the columns to the Key object
first and then add it to the Keys collection.


Paul ~~~ pclement RemoveThis @ameritech.net
Microsoft MVP (Visual Basic)