Microsoft KB Archive/258013

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

Article ID: 258013

Article Last Modified on 7/13/2004

-

APPLIES TO


 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.1 Service Pack 1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7
 * Microsoft Data Access Components 2.8

-



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.



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= and 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= ;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.

