469,313 Members | 2,603 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,313 developers. It's quick & easy.

Determine Primary Key through VBA

Hi all,

I have a situation where I need to determine a specific table's
primary key, and then output it.

I have tried the Database Object, and the Record Object, but I've had
no luck.

Ideally there would be something like,

Record(0).index, or .type, or something that would determine the
primary key.

Anyone have any ideas?

Mark

Jul 18 '07 #1
1 4521
Loop through the Indexes collection to find the one that has the Primary
property set. Note that the primary key consist of more than one field.

The example below shows how to loop the indexes, and assigns upper case P
for the first field in the primary key, or the lower case p for subsequent
fields. It's part of this routine:
http://allenbrowne.com/AppRelReportCode.html

Note that if you were trying to identify the AutoNumber field, you will need
to check the Attributes of the Field in the TableDef. There's an example in
the DescribeFields() function on the same page (above.)

Private Function DescribeIndexField(tdf As DAO.TableDef, strField As String)
As String
'Purpose: Indicate if the field is part of a primary key or unique
index.
'Return: String containing "P" if primary key, "U" if uniuqe index,
"I" if non-unique index.
' Lower case letters if secondary field in index. Can have
multiple indexes.
'Arguments: tdf = the TableDef the field belongs to.
' strField = name of the field to search the Indexes for.
Dim ind As DAO.Index 'Each index of this table.
Dim fld As DAO.Field 'Each field of the index
Dim iCount As Integer
Dim strReturn As String 'Return string

For Each ind In tdf.Indexes
iCount = 0
For Each fld In ind.Fields
If fld.Name = strField Then
If ind.Primary Then
strReturn = strReturn & IIf(iCount = 0, "P", "p")
ElseIf ind.Unique Then
strReturn = strReturn & IIf(iCount = 0, "U", "u")
Else
strReturn = strReturn & IIf(iCount = 0, "I", "i")
End If
End If
iCount = iCount + 1
Next
Next

DescribeIndexField = strReturn
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"mark_aok" <ma******@hotmail.comwrote in message
news:11*********************@z24g2000prh.googlegro ups.com...
Hi all,

I have a situation where I need to determine a specific table's
primary key, and then output it.

I have tried the Database Object, and the Record Object, but I've had
no luck.

Ideally there would be something like,

Record(0).index, or .type, or something that would determine the
primary key.

Anyone have any ideas?

Mark
Jul 18 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by jaYPee | last post: by
15 posts views Thread by Robby Russell | last post: by
6 posts views Thread by Robin Lawrie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.