Find table key fields using ADO

Posted on 2000-02-29
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?
Question by:antonioavmelo
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 2570002
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

Expert Comment

ID: 2570041
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.

Accepted Solution

PatrickVD earned 25 total points
ID: 2570232
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....

set rsResult = Nothing


The rsResult will contain a recordset that has the following fields :
(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:
(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 !

Author Comment

ID: 2572670

A high quality answer. Nice working example too.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question