Solved

Find table key fields using ADO

Posted on 2000-02-29
4
245 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
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…

706 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

18 Experts available now in Live!

Get 1:1 Help Now