Primary Key

[Kim]

Is there a way using the fields collection to obtain which field is the
primary key? I can obtain the name of the field and the value and I want to
know if it is the primary key or not.

Thanks!

 

[JohnFol]

No, as the keys are held in the indexes collection.

Here's a quick bit of code for you that will run in northwind to show what I
mean

Dim db As Database
Set db = CurrentDb
Dim td As TableDef
Set td = db.TableDefs("Order Details")

Dim id As Index
Dim fld As Field

For Each id In td.Indexes
If id.Primary Then
For Each fld In id.Fields
Debug.Print fld.Name
Next
End If
Next

 

[Kim]

That is what I thought. If I wanted to keep track of how many primary keys
are present, how would I do that?

 

[John Vinson]

That is what I thought. If I wanted to keep track of how many primary keys
are present, how would I do that?

That's easy - there can be either zero or one. <g>

td.Indexes("PrimaryKey").Fields.Count

will give the number of fields in that index.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

That's easy - there can be either zero or one. <g>

td.Indexes("PrimaryKey").Fields.Count

will give the number of fields in that index.

I've been reminded that - though Access routinely uses the name
PrimaryKey - this is not required. It would be safer to use

For Each idx In td.Indexes
If idx.Primary Then
MsgBox "The Primary Key has " & idx.Fields.Count & " fields"
Exit For
End If
Next idx

Thanks to Douglas Steele for the heads-up!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps