ADO Primary Key Column Name

[NJLDoc]

Is there a way to loop through the fields (columns) using ADO/ADOX of a table looking for the primary keys within that table and return the field (column) name. I have been able, using ADOX to loop through the table indexes and identify if the found index is a primary key but cannot obtain the field or column name of that index.

 

[CCLINT]

From ADOX 2.7 examples

Sub PrimaryKeyX()

Dim catNorthwind As New ADOX.Catalog
Dim tblNew As New ADOX.Table
Dim idxNew As New ADOX.Index
Dim idxLoop As New ADOX.Index
Dim colLoop As New ADOX.Column

' Connect the catalog
catNorthwind.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"data source=c:\Program Files\" & _
"Microsoft Office\Office\Samples\Northwind.mdb;"

' Name new table
tblNew.Name = "NewTable"

' Append a numeric and a text field to new table.
tblNew.Columns.Append "NumField", adInteger, 20
tblNew.Columns.Append "TextField", adVarWChar, 20

' Append new Primary Key index on NumField column
' to new table
idxNew.Name = "NumIndex"
idxNew.Columns.Append "NumField"
idxNew.PrimaryKey = True
idxNew.Unique = True
tblNew.Indexes.Append idxNew

' Append an index on Textfield to new table.
' Note the different technique: Specifying index and
' column name as parameters of the Append method
tblNew.Indexes.Append "TextIndex", "TextField"

' Append the new table
catNorthwind.Tables.Append tblNew

With tblNew

Debug.Print tblNew.Indexes.Count & " Indexes in " & _
tblNew.Name & " Table"

' Enumerate Indexes collection.
For Each idxLoop In .Indexes

With idxLoop
Debug.Print "Index " & .Name
Debug.Print " Primary key = " & .PrimaryKey
Debug.Print " Unique = " & .Unique

' Enumerate Columns collection of each Index
' object.
Debug.Print " Columns"
For Each colLoop In .Columns
Debug.Print " " & colLoop.Name
Next colLoop

End With

Next idxLoop

End With

' Delete new table as this is a demonstration
catNorthwind.Tables.Delete tblNew.Name
Set catNorthwind = Nothing

End Sub
[/b][/i][/u]*******************************************************
_{General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[NJLDoc]

This code works for adding a new table and assigning indexes etc. However, I am missing something in my understanding. I have a table already with four fields for example with two that are primary keys. I want to interrogate this existing table for the names of the fields (columns) that are primary keys.

 

[CCLINT]

The look at the section starting with: With tblNew

This will return the Index name, PrimaryKey and Column names.... [/b][/i][/u]*******************************************************
_{General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[NJLDoc]

Thanks CCLINT for your help. Going through the sample you offered along with the 'with table' reference pointed out what it was I was missing. The index I had dimensioned was As Index and not AS ADOX.Index. Sometimes I can look at code for hours and not pick out the obvious. Thanks again.