Get tables foreign keys with ADO.

have a look at the OpenSchema method from the ADO connection object.

Use ADOX (ADO Ext.), here is sample, connection string is for MDB

' add refs: Microsoft ActiveX Data Objects 2.x Library
'   and Microsoft ADO Ext. 2.x for DDL and Security
'
' Form1, add textbox txtCnn, button cmdOpen,
'   listbox lstTables, textbox Text1 (MultiLine=True)
Option Explicit
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim cat As ADOX.Catalog

Private Sub Form_Load()
    txtCnn.Text = "provider=Microsoft.Jet.OLEDB.4.0;data source=c:\biblio.mdb" ' <-- Change
End Sub

Private Sub cmdOpen_Click()
    Dim i As Long
   
    Set cnn = New ADODB.Connection
    'Open the Connection w/ Provider Info
    cnn.Open txtCnn.Text, "Admin", ""           ' <--- CHANGE
   
    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = cnn
   
    ' fill tables list
    lstTables.Clear
    For i = 0 To cat.Tables.Count - 1
        If cat.Tables(i).Type = "TABLE" Then
            lstTables.AddItem cat.Tables(i).Name
        End If
    Next
End Sub

Private Sub lstTables_Click()
    Dim i As Long, j As Long, numkeys As Integer, TMP As String
    Dim ctab As ADOX.Table, cc As ADOX.Column, k As ADOX.Key
   
    If lstTables.ListIndex < 0 Then Exit Sub    ' nothing was selected
   
    Caption = lstTables.List(lstTables.ListIndex)
    Set ctab = cat.Tables(lstTables.List(lstTables.ListIndex))
    Text1.Text = ""
   
    On Error Resume Next
    numkeys = ctab.Keys.Count
    For i = 0 To numkeys - 1
        Set k = ctab.Keys(i)
        ' show key name and type
        TMP = "Key " & i + 1 & ". Name='" & k.Name & "'"
        If k.Type = adKeyForeign Then
            TMP = TMP & " Foreign "
        ElseIf k.Type = adKeyPrimary Then
            TMP = TMP & " Primary "
        ElseIf k.Type = adKeyUnique Then
            TMP = TMP & " Unique "
        End If
       
        ' columns in this key
        TMP = TMP & "("
        For j = 0 To k.Columns.Count - 1
            TMP = TMP & String(Abs(j > 0), ",") & _
            ctab.Name & "." & k.Columns(j).Name
            If Len(k.RelatedTable) Then
                TMP = TMP & "=" & k.RelatedTable & "." & k.Columns(j).RelatedColumn
            End If
        Next
        TMP = TMP & ")"
        If Len(k.RelatedTable) Then
            TMP = TMP & " RelatedTable='" & k.RelatedTable & "' rule=" & k.UpdateRule
        End If
        Text1.SelText = TMP & vbCrLf & vbCrLf
    Next
End Sub

Ameba, emoreau,
I have tried both suggestions but none of them worked.

Is there any other option?

Did you try my code with .MDB?
Maybe you didn't add the right references...

I can post a zip of the project if you want.

OpenSchema is 'light' method to get info about db structure.

ADOX (Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security) has more power, it is the DLL which is also used with ADO.NET, i.e. there isn't anything better or newer...

>Is there any other option?
I use DAO code.

I use it and I have no problem!

Ameba,

yes I tried your code with .MDB but I used Microsoft Access ODBC instead Microsoft Jet Engine. I dont' have Microsoft Jet Engine but I think this Engine only works for Microsoft databases.

numkeys = ctab.Keys.Count
In my code numkeys was 0. There were no keys.

Could you please send me this zip code to try it?
use this email: tachundo1@hotmail.com

Thanks

Not all ADO drivers support all ADO or ADOX features.

If you want excellent tool with many native drivers, try ERWIN 4.0
http://www.ca.com
http://www.cai.com/evaluate/download/erwin_standard.htm 
(Getting the 30-days licence key is a bit complex)

Here is the project and screenshot:
http://www.geocities.com/ameba_vb/temp/keys.html
If you have VB6 installed, find BIBLIO.MDB and copy it to c:\ - that should work with given connection string.

I used "provider=Microsoft.Jet.OLEDB.4.0;" because:
ADOX is fully supported with the Microsoft OLE DB Provider for the Microsoft Jet Database Engine.


This is from "adoxreadme.txt" (in \Program Files\Common Files\System\ado\)
------------------------------
3.2 Using ADOX with Microsoft OLE DB Providers

Certain features of ADOX are unsupported, depending upon your OLE DB data provider. ADOX is fully supported with the Microsoft OLE DB Provider for the Microsoft Jet Database Engine. For the Microsoft SQL Server OLE DB Provider, the Microsoft OLE DB Provider for ODBC, or the Microsoft OLE DB Provider for Oracle see below. ADOX is not supported by any other Microsoft OLE DB providers.

Microsoft SQL Server OLE DB Provider

All ADOX objects, collections, properties, and methods are supported except for those listed here:

Catalog: Create method.
Tables: Properties for existing tables are read-only (properties for new tables can be read/write).
Views: Not supported.
Procedures: Append method, Delete method, Command property.
Keys: Append method, Delete method.
Users: Not supported.
Groups: Not supported.


Microsoft OLE DB Provider for ODBC

All ADOX objects, collections, properties, and methods are supported except for those listed here:

Catalog: Create method.
Tables: Append method, Delete method. Properties for existing tables are read-only (properties for new tables can be read/write).
Views: Append method, Delete method, Command property.
Procedures: Append method, Delete method, Command property.
Indexes: Append method, Delete method.
Keys: Append method, Delete method.
Users: Not supported.
Groups: Not supported.


Microsoft OLE DB Provider for Oracle

All ADOX objects, collections, properties, and methods are supported except for those listed here:

Catalog: Create method.
Tables: Append method, Delete method. Properties for existing tables are read-only (properties for new tables can be read/write).
Views: Append method, Delete method, Command property.
Procedures: Append method, Delete method, Command property.
Indexes: Append method, Delete method.
Keys: Append method, Delete method.
Users: Not supported.
Groups: Not supported.
------------------------------

any progress?

I'm going to try using ADOX with Microsoft Jet Engine and I'll tell you if it works.

Ameba,

I've been using your proposal, and it works if I use this connection string
txtCnn.Text = "provider=Microsoft.Jet.OLEDB.4.0;data source=c:\biblio.mdb"

but I need it to work also when I specify an ODBC

i.e. txtCnn.Text = "provider=Microsoft.Jet.OLEDB.4.0;data source=biblio"   where biblio would be and ODBC

is this possible?

when using ODBC, the provider is not Jet. see http://www.able-consulting.com/ADO_Conn.htm#ODBCDSNConnections for examples

Then what connection string should I use?? Because I have tried this: "Provider=MSDASQL; DSN=biblio" but I can't get the catalog keys.

Which provider is the biblio DNS is using? You have many example of connection strings at http://www.able-consulting.com/ADO_Conn.htm

Biblio ODBC is using Microsoft Access Driver (*.mdb)

you should use this connection string: http://www.able-consulting.com/ADO_Conn.htm#OLEDBProviderForMicrosoftJet

This question appears to be abandoned. A question regarding it will be left in the Community Support
area; if you have any comment about the question, please leave it here.

Unless there is objection or further activity, one of the moderators will be asked to splits points between  <emoreau> and <ameba>.

The link to the Community Support area is:
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt

DO NOT ACCEPT THIS COMMENT AS AN ANSWER.

Thanks, points have been split.

Points for ameba -> https://www.experts-exchange.com/jsp/qShow.jsp?qid=20309580

Moondancer - EE Moderator

Thanks, Moondancer

My contribution to KB here is 50% (50% of 500 = 250) or nothing.  Not 50 pts.

Sincere apologies, ameba, that has been fixed.  I was roadblocked, because the Asker's profile shows an invalid address and had no options to correct this existing question value and have asked Administration to correct the address in the profile, then downgrade this question value to 250, and debit Asker 250 from available.

In the meantime, I have corrected your Points for Q from my points until this issue can be resolved.

Sorry also for the delay, I have not received ONE Email notificaton of this activity, accessed via Power Search recent activity to monitor (Engineering advised).

Moondancer - EE Moderator

Thanks all, ComTech, Administrative liaison has fixed the problem, and this question value now corrected to 250 points.
Moondancer - EE Moderator

Thanks!