Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

How To Use ADOX to Determine If a Primary Key Exists on a Table


View products that this article applies to.

This article was previously published under Q258013

↑ Back to the top


Summary

In certain cases, you may want to determine if a Primary Key is defined on an underlying database table and if so, what columns are used in that Primary Key. You can obtain this information by using Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX), which is an extension to ActiveX Data Objects (ADO) that allows the manipulation of the database schema.

↑ Back to the top


More information

The following code creates a table in a database that has a Primary Key column, and then uses ADOX to retrieve this information from the database schema.

Note Not all OLE DB providers support the interfaces that are required to support ADOX methods. If the OLEDB provider you are using does not support ADOX methods, then you need to use an alternative method to obtain this information.

To run the following code, modify the connection string so that it points to your database and do the following:
  1. Create a new Microsoft Visual Basic Standard EXE project. Form1 is created by default.
  2. On the Project menu, choose References, and then add a reference to the Microsoft ActiveX Data Objects and Microsoft ADO Ext. for DDL and Security libraries.
  3. Paste the following code into the Form_Load() event:

    Note You must change User ID=<username> and Password=<strong password> to the correct values before you run this code. Make sure that the User ID has the appropriate permissions to perform this operation on the database.
    Const DB = "Provider=SQLOLEDB.1;User ID=<username>;Password=<strong password>;Initial Catalog=pubs;Data Source=Your_SQL_Server"
        
    'include reference to ADO and ADOX
    Dim cn As ADODB.Connection
    Dim cat As ADOX.Catalog
    Dim idx As ADOX.Index
        
    Set cat = New ADOX.Catalog
    Set cn = New ADODB.Connection
    cn.Open DB
        
    On Error Resume Next
    SQL = "DROP TABLE PKTEST1"
    cn.Execute SQL
        
    On Error GoTo 0
    SQL = "CREATE TABLE PKTEST1 (f1 INT PRIMARY KEY, f2 INT)"
    cn.Execute SQL
        
    Set cat.ActiveConnection = cn
        
    'Check all indexes on the table for a primary key
    For Each idx In cat.Tables("PKTEST1").Indexes
            If idx.PrimaryKey = True Then
            Debug.Print "INDEX  NAME: " & idx.Name
                
            'Show all columns that make up the index
            Debug.Print "consists of the following columns:"
            For i = 0 To idx.Columns.Count - 1
                Debug.Print idx.Columns(i).Name
            Next
                
        End If
            
    Next
    					
  4. Run the code, and note that it creates a new table name, PKTEST1, in your database, queries the database to retrieve the Primary Key information for that Table, and then displays it in the Immediate window.

↑ Back to the top


References

For more information on ADOX, see the ADOX Programmers Reference Guide in the MDAC SDK available on the following Microsoft Web site at:

↑ Back to the top


Keywords: KB258013, kbhowto

↑ Back to the top

Article Info
Article ID : 258013
Revision : 7
Created on : 7/13/2004
Published on : 7/13/2004
Exists online : False
Views : 419