How to list primary columns in an Access table by kimnju

VIEWS: 2 PAGES: 3

More Info
									How to list the primary key columns in an Access table
By Susan Sales Harkins

Version 1.0 October 24, 2008

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.

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, _

Page 1 Copyright © 2008 CNET Networks, Inc., a CBS Company. All rights reserved. TechRepublic is a registered trademark of CNET Networks, Inc For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

How to list the primary key columns in an Access table "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. 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 A

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 Figure B 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.

Page 2 Copyright © 2008 CNET Networks, Inc., a CBS Company. All rights reserved. TechRepublic is a registered trademark of CNET Networks, Inc For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

How to list the primary key columns in an Access table

Additional resources
TechRepublic's Downloads RSS Feed Sign up for the Downloads at TechRepublic newsletter Sign up for our IT Leadership Newsletter Check out all of TechRepublic's free newsletters How do I... Add custom error handling to my Access forms and reports? How do I... fill Word form fields with Access data? How do I... Retrieve a random set of records in Microsoft Access?

Version history
Version: 1.0 Published: October 24, 2008

Tell us what you think
TechRepublic downloads are designed to help you get your job done as painlessly and effectively as possible. Because we're continually looking for ways to improve the usefulness of these tools, we need your feedback. Please take a minute to drop us a line and tell us how well this download worked for you and offer your suggestions for improvement. Thanks! —The TechRepublic Content Team

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.

Page 3 Copyright © 2008 CNET Networks, Inc., a CBS Company. All rights reserved. TechRepublic is a registered trademark of CNET Networks, Inc For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html


								
To top