We help IT Professionals succeed at work.

Find table key fields using ADO

Last Modified: 2011-10-03

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?
Watch Question

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

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.
This one is on us!
(Get your first solution completely free - no credit card required)



A high quality answer. Nice working example too.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.