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

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.