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

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:

Create a new Microsoft Visual Basic Standard EXE project. Form1 is created by default.
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.

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

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