Solved

Get tables foreign keys with ADO.

Posted on 2002-04-24
23
608 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 70

Expert Comment

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

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 70

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 70

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
 
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 70

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 70

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 70

Expert Comment

by:Éric Moreau
ID: 7021569
0
 
LVL 70

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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
using web browser with BING 40 122
Graphics32 under Delphi 10.1 Berlin 2 68
IF ELSE Statement in Excel Macro VBA 16 65
how to Classify of email (pdf attachments) 7 36
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

821 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