Working with key values is serious work, and assigning a primary key is just the beginning of the process. If you need to manipulate a primary key programmatically, you need to know the columns that the key comprises. There are easy ways to do that manually, but doing so programmatically can prove useful if the task is part of the application’s internal workings or you’re dealing with external tables.
Note: This information is also available as a PDF download, along with a BAS file containing the code listing.
You might think that listing the columns in a primary key would be easy, but that’s not the case. Perhaps the most efficient process is to use ADOX objects. Specifically, the function in Listing A uses ADOX catalog, table, index, and column objects. A series of For…Each loops and If…Then…Else statements cycle through three collections to determine the table’s primary key index and then build a string variable from the names of the columns that belong to that key. All that, just to list a few columns!
Function ListPK(tbl As String) As String
'List primary keys for passed table. 'Must reference ADOX library: 'Microsoft ADO Ext. 2.8 for DDL and Security. Dim cat As New ADOX.Catalog Dim tblADOX As New ADOX.Table Dim idxADOX As New ADOX.Index Dim colADOX As New ADOX.Column
cat.ActiveConnection = CurrentProject.AccessConnection
On Error GoTo errHandler
For Each tblADOX In cat.Tables
If tblADOX.Name = tbl Then If tblADOX.Indexes.Count <> 0 Then For Each idxADOX In tblADOX.Indexes With idxADOX If .PrimaryKey Then For Each colADOX In .Columns ListPK = colADOX.Name & ", " & ListPK Next End If End With Next End If End If Next
If ListPK = "" Then ListPK = "No primary key" Else ListPK = Left(ListPK, Len(ListPK) - 2) End If
Set cat = Nothing Set tblADOX = Nothing Set idxADOX = Nothing Set colADOX = Nothing Exit Function
errHandler: MsgBox Err.Number & ": " & Err.Description, vbOKOnly, _ "Error" Set cat = Nothing Set tblADOX = Nothing Set idxADOX = Nothing Set colADOX = Nothing
To enter this function, launch the Visual Basic Editor (VBE) by pressing Alt + F11. Choose Module from the Insert menu, enter the code, and save the module. This code uses ADOX objects, so be sure to reference the Microsoft ADO Ext. 2.8 For DDL And Security library. Choose References from the Tools menu, select the library (Figure A), and click OK.
Reference the ADOX library.
To execute the function, open the Immediate window by pressing Ctrl + G. Type the following line:
where tablename is the table for which you’re listing primary key columns. Now, press Enter. Figure B shows the results of passing the Northwind table Employees to ListPK(). (Northwind is an example database that comes with Access.)
Use the Immediate window to pass a table to the ListPK() function.
The first For…Each loop cycles through the Tables collection looking for tbl, the passed string, which in this case is Employees. When the code finds a match, the next statement makes sure that Employees has at least one index to examine. If it does, the code loops through the Indexes collection until it finds the primary key index. The next For…Each loop builds a string that includes the names of all the columns in the primary key in column1, column2, column3 format. Finally, the function returns that string.
If a table has an index but no primary key, the function returns the string “No primary key.” If the table has no index, the function returns the string “No primary key.” You could just as easily use a subprocedure to print the results to the Immediate window.
Primary keys are an integral part of any relational database. You can use ListPK() while debugging a new database. With some customization, you could use it to manipulate primary keys programmatically.
Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader’s Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner’s Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at firstname.lastname@example.org.