How to retrieve FOREIGN key information from an ORACLE database using Visual Basic (and preferably ADO/ADOX)

From: Shared PC <Internet_at_centbldg.demon.co.uk>
Date: Thu, 27 Jan 2000 12:35:46 -0000
Message-ID: <948976460.19446.0.nnrp-03.c2de9529@news.demon.co.uk>

Can anyone please give me some guidance as to a way in which it is possible to read from an ORACLE database the foreign keys.

If it is not possible via ADO or ADOX to retrieve this information does anyone know of any other way to determine this information - as I am hoping to read a database structure properly without any hard coding of structures.

The ODBC oracle driver being used to execute the code below is the Microsoft one that is supplied with Visual Basic 6 SP3. The line of code that raises an 'Unsupported function with driver' message in Oracle is highlighted with **:

    Dim ky As New ADOX.Key
    Dim cat As New ADOX.Catalog
    Dim cnn As New ADODB.Connection
    Dim tbl As New ADOX.Table

'Oracle Connection String follows

    cnn.Provider = "MSDAORA.1"
    cnn.Open "Data Source=mydsn;User Id=myid;Password=mypwd;"     Set cat.ActiveConnection = cnn

    For Each tbl In cat.Tables

        Debug.Print "Looping through each table entry"

        If tbl.Type = "TABLE" Then
            Debug.Print "Found a user table"



**            For Each ky In tbl.Keys


**                Debug.Print "Looping through each of the keys"


**            Next ky


        End If

    Next tbl

If instead of the above provider and open connection lines the following are used to connect
to an ACCESS database the above code functions correctly:

   cnn.Provider = "Microsoft.Jet.OLEDB.4.0"    cnn.Open "Data Source=c:\northwind.mdb"

Anyone have any ideas? Received on Thu Jan 27 2000 - 06:35:46 CST