Please help with primary key

[Guest]

How can I get the name of the primary keys of all tables in my database
programatically?
thanks
Al

 

[Douglas J. Steele]

Do you really mean the name of the primary keys, or the names of the fields
in each of them?

For the names of the primary keys, you can use:

Sub ListPKs()
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim idxCurr As DAO.Index
Dim strMessage As String

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
strMessage = vbNullString
For Each idxCurr In tdfCurr.Indexes
If idxCurr.Primary = True Then
strMessage = "The PK for " & tdfCurr.Name & _
" is " & idxCurr.Name
Exit For
End If
Next idxCurr
If Len(strMessage) = 0 Then
strMessage = tdfCurr.Name & " doesn't have a PK"
End If
Debug.Print strMessage
End If
Next tdfCurr

Set dbCurr = Nothing

End Sub

To get a list of the fields in each PK, you can use

Sub ListPKs()
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim idxCurr As DAO.Index
Dim fldCurr As DAO.Field
Dim strMessage As String

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
strMessage = vbNullString
For Each idxCurr In tdfCurr.Indexes
If idxCurr.Primary = True Then
strMessage = "The PK for " & tdfCurr.Name & _
" is " & idxCurr.Name & _
", which consists of:" & vbCrLf
For Each fldCurr In idxCurr.Fields
strMessage = strMessage & " " & _
fldCurr.Name & vbCrLf
Next fldCurr
Exit For
End If
Next idxCurr
If Len(strMessage) = 0 Then
strMessage = tdfCurr.Name & " doesn't have a PK"
End If
Debug.Print strMessage
End If
Next tdfCurr

Set dbCurr = Nothing

End Sub

 

[Roger Carlson]

The following code will print the primary keys of all tables to the
Immediate Window. You'll have to modify it if you want to use it in some
other way. Please note that tables sometimes have compound primary keys and
you'll have to deal with that. Anyway:

Public Sub FindPrimaryKeys()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field
Set db = CurrentDb
Dim i As Integer

For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "MSYS" Then
For Each idx In tdf.Indexes
If idx.Primary = True Then
For Each fld In idx.Fields
Debug.Print tdf.Name & " - " & fld.Name
Next
End If
Next
End If
Next

End Sub

Note: code will ignore the Access System tables.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Marshall Barton]

How can I get the name of the primary keys of all tables in my database
programatically?

Your question is logically inconsistent. A primary key is
an Index, not a field. This may seem irrellevant in simple
cases, but a primary key can consist of up to ten fields so
the distinction is important to doing what you want.

In any case you will need to loop through the TableDefs
collection to get to each TableDef object. Then loop
through the TableDef's Indexes collection to find the index
with its Primary property set to True. Then you can loop
throught the index's Fields collection to obtain a list of
the fields that comprise the index.

 

[Guest]

thanks it works

The following code will print the primary keys of all tables to the
Immediate Window. You'll have to modify it if you want to use it in some
other way. Please note that tables sometimes have compound primary keys and
you'll have to deal with that. Anyway:

Public Sub FindPrimaryKeys()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field
Set db = CurrentDb
Dim i As Integer

For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "MSYS" Then
For Each idx In tdf.Indexes
If idx.Primary = True Then
For Each fld In idx.Fields
Debug.Print tdf.Name & " - " & fld.Name
Next
End If
Next
End If
Next

End Sub

Note: code will ignore the Access System tables.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L