Solved

Find table key fields using ADO

Posted on 2000-02-29
4
246 Views
Last Modified: 2011-10-03
Hi

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?
0
Comment
Question by:antonioavmelo
4 Comments
 
LVL 2

Expert Comment

by:nfernand
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
End
0
 
LVL 6

Expert Comment

by:Marine
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.
0
 
LVL 1

Accepted Solution

by:
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....

rsResult.Close
set rsResult = Nothing
conDB.Close


-----

The rsResult will contain a recordset that has the following fields :
PK_TABLE_CATALOG
PK_TABLE_SCHEMA
PK_TABLE_NAME
PK_COLUMN_NAME
FK_TABLE_CATALOG
FK_TABLE_SCHEMA
FK_TABLE_NAME
FK_COLUMN_NAME
(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:
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
(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 !
0
 

Author Comment

by:antonioavmelo
ID: 2572670

A high quality answer. Nice working example too.
Thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

932 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

14 Experts available now in Live!

Get 1:1 Help Now