Data Management

How to list the primary key columns in an Access table

You can get a list of the columns in a primary key manually, but depending on the task at hand, you may want to handle it programmatically instead. Susan Harkins shares a VBA function that uses ADOX objects to obtain the column information.

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.

The code

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!

Listing A

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
End Function
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.

Figure A

references

Reference the ADOX library.

To execute the function, open the Immediate window by pressing Ctrl + G. Type the following line:

ListPK("tablename")
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.)

Figure B

Immediate window

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.

Print keys

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 ssharkins@gmail.com.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

2 comments
brigzy
brigzy

Hello Susan, I am trying this out with an Access database (2003). When to code tries to reference the index's columns property an error occurs: 'Object or provider is not capable of performing requested operation' The same error occurs when I try to connect to SQL Server. Can I ask if you have an idea as to the cause .. Richard

brigzy
brigzy

It seems to be a problem only if I use a DSN. If I use a connection string then it works. So in other words ODBC = problem, OLEDB - it's ok. Hope this helps anyone trying this. Richard http://leansoftware.net

Editor's Picks