Find table key fields using ADO

Hi

In DAO, I can easily find the set of fields that belong to the key of some table.

How can I do this through ADO?
I tried to use some "select * from t" and look at each field, but had no luck...

What about foreign keys?
antonioavmeloAsked:
Who is Participating?
 
PatrickVDConnect With a Mentor Commented:
Sorry guys... but you CAN do it with ADODB also !
All you need is the OpenSchema method of the ADODB.Connection object.

I tested this code with the Northwind database that comes with SQL Server 7.0

-----

Dim conDb as ADODB.Connection
Dim rsResult as ADODB.RecordSet

Set conDB = New ADODB.Connection

conDB.Open strYourConnectionString, strYourUserID, strYourPassword

Set rsResult = conDB.OpenSchema(adSchemaForeignKeys)

' Some code that loops through the returned records....

rsResult.Close
set rsResult = Nothing
conDB.Close


-----

The rsResult will contain a recordset that has the following fields :
PK_TABLE_CATALOG
PK_TABLE_SCHEMA
PK_TABLE_NAME
PK_COLUMN_NAME
FK_TABLE_CATALOG
FK_TABLE_SCHEMA
FK_TABLE_NAME
FK_COLUMN_NAME
(there are more columns, but these seem the most relevant to me! more details can be found on MSDN)

These field names are quit straightforward... so a little bit of filtering on this resultset (based on table_name for instance) might give you a bright look on the foreign keys of a certain table !

If you're interested in the PrimaryKeys only, there is a very similar way of processing. You just use the 'adSchemaPrimaryKeys' enum constant instead of the 'adSchemaForeignKeys' in this sample. Nevertheless, the resulting recordset will be slightly different and contain the following columns:
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
(there are more columns, but i think these are relevant to your question)

Again, I tested this agains a SQL7 database... It is always possible that the OLEDB provider does not implement these OpenSchema options... but at least the OLEDB provider for SQL Server 7 does it!

Hope this helps ya out !
0
 
nfernandCommented:
Somebody told me something aboput ADOX.
You must set a reference to "Microsoft ADO Ext 2.1 for DDL and Security".
This way you will have objects like:

Dim col as ADOX.Column, etc.

You will have the properties you need. This is the code sample EMOREAU gave me:

Private Sub Command1_Click()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim colprop As ADOX.Property
     
    On Error Resume Next
    Kill "c:\test.mdb"
    On Error GoTo 0

    Set cat = New ADOX.Catalog
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=D:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb;"

    Set tbl = cat.Tables("Employees")

    For Each col In tbl.Columns
        List1.AddItem col.Name & Space(2) & "Type: " & col.Type
        For Each colprop In col.Properties
            List1.AddItem Space(3) & _
                          colprop.Name & _
                          " -- " & _
                          "Type: " & colprop.Type & _
                          " -- " & _
                          "Value: " & colprop.Value
        Next colprop
    Next col
End
0
 
MarineCommented:
My friend you cannot do it easily in dao from your example as well. You would have to use TableDefs Ojbect which you didn't say anything about. In ADO you would need to use ADOX and columns collection.
0
 
antonioavmeloAuthor Commented:

A high quality answer. Nice working example too.
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.