?
Solved

Find table key fields using ADO

Posted on 2000-02-29
4
Medium Priority
?
265 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 100 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
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…
Suggested Courses
Course of the Month15 days, 2 hours left to enroll

839 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