Solved

Get tables foreign keys with ADO.

Posted on 2002-04-24
23
592 Views
Last Modified: 2013-11-23
Hi,

I need to know how to get tables foreign keys using ADO. It must work for as much databases as possible (Orale, Informix, SQL Server, Access,...).

I'm using Visual Basic Studio 6.0, and ADO 2.7.

I already have tried using openschema but it doesn't work (I have only tried with Access).

Thanks
0
Comment
Question by:javiertb
  • 8
  • 6
  • 6
  • +1
23 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6966885
have a look at the OpenSchema method from the ADO connection object.
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 250 total points
ID: 6966894
have you used this syntax:
Set rstInfo = pconConnection.OpenSchema(adSchemaForeignKeys, Array(Empty, Empty, Empty, Empty, Empty, tblSchema.Name))
0
 
LVL 15

Expert Comment

by:ameba
ID: 6967346
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
0
 
LVL 2

Author Comment

by:javiertb
ID: 6969804
Ameba, emoreau,
I have tried both suggestions but none of them worked.

Is there any other option?

0
 
LVL 15

Expert Comment

by:ameba
ID: 6969867
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.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6970366
I use it and I have no problem!
0
 
LVL 2

Author Comment

by:javiertb
ID: 6971222
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
0
 
LVL 15

Expert Comment

by:ameba
ID: 6971700
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.
0
 
LVL 15

Expert Comment

by:ameba
ID: 6974704
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.
------------------------------
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6985015
any progress?
0
 
LVL 2

Author Comment

by:javiertb
ID: 6989598
I'm going to try using ADOX with Microsoft Jet Engine and I'll tell you if it works.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 2

Author Comment

by:javiertb
ID: 7009666
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?
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7009846
when using ODBC, the provider is not Jet. see http://www.able-consulting.com/ADO_Conn.htm#ODBCDSNConnections for examples
0
 
LVL 2

Author Comment

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

Expert Comment

by:Éric Moreau
ID: 7015324
Which provider is the biblio DNS is using? You have many example of connection strings at http://www.able-consulting.com/ADO_Conn.htm
0
 
LVL 2

Author Comment

by:javiertb
ID: 7021558
Biblio ODBC is using Microsoft Access Driver (*.mdb)
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7021569
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7060703
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:
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt

DO NOT ACCEPT THIS COMMENT AS AN ANSWER.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7065905
Thanks, points have been split.

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

Moondancer - EE Moderator
0
 
LVL 15

Expert Comment

by:ameba
ID: 7066241
Thanks, Moondancer

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

Expert Comment

by:Moondancer
ID: 7079100
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
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7079127
Thanks all, ComTech, Administrative liaison has fixed the problem, and this question value now corrected to 250 points.
Moondancer - EE Moderator
0
 
LVL 15

Expert Comment

by:ameba
ID: 7079292
Thanks!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now