Is field primary key or does it have a unique index

  • Thread starter Thread starter Dale Fye
  • Start date Start date
D

Dale Fye

Looking for a quick and easy way to determine what the PK field (assuming
only one field in the PK) is in a recordset, or determine whether a field has
a unique index.
 
apparently, recordsets don't have indices.

I wrote a little function to return the name of the field(s) in a table that
belong to the primary key.

Public Function fnPKFieldName(TableName As String) As String

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field

fnPKFieldName = ""
Set db = CurrentDb
Set tdf = db.TableDefs(TableName)
For Each idx In tdf.Indexes
If idx.Name = "PrimaryKey" Then
For Each fld In idx.Fields
fnPKFieldName = fnPKFieldName & ", " & fld.Name
Next
Exit For
End If
Next

If Len(fnPKFieldName) > 0 Then fnPKFieldName = Mid(fnPKFieldName, 3)

Set fld = Nothing
Set idx = Nothing
Set tdf = Nothing

End Function

I also wrote a function to determine whether a table has unique indices
besides the primarykey.

Public Function fnTableHasUniqueIndices(TableName As String) As Boolean

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index

fnTableHasUniqueIndices = False
Set db = CurrentDb
Set tdf = db.TableDefs(TableName)
For Each idx In tdf.Indexes
If idx.Unique = True And idx.Name <> "PrimaryKey" Then
fnTableHasUniqueIndices = True
Exit For
End If
Next

Set idx = Nothing
Set tdf = Nothing
Set db = Nothing

End Function

Together, these functions will help me develop a generic CopyRecord
function, that will allow me to copy records in tables that contain a single
PK field, and don't have other unique indices assigned.
 
Dale Fye said:
apparently, recordsets don't have indices.

I wrote a little function to return the name of the field(s) in a table
that
belong to the primary key.

Public Function fnPKFieldName(TableName As String) As String

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field

fnPKFieldName = ""
Set db = CurrentDb
Set tdf = db.TableDefs(TableName)
For Each idx In tdf.Indexes
If idx.Name = "PrimaryKey" Then
For Each fld In idx.Fields
fnPKFieldName = fnPKFieldName & ", " & fld.Name
Next
Exit For
End If
Next

If Len(fnPKFieldName) > 0 Then fnPKFieldName = Mid(fnPKFieldName, 3)

Set fld = Nothing
Set idx = Nothing
Set tdf = Nothing

End Function

I also wrote a function to determine whether a table has unique indices
besides the primarykey.

Public Function fnTableHasUniqueIndices(TableName As String) As Boolean

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index

fnTableHasUniqueIndices = False
Set db = CurrentDb
Set tdf = db.TableDefs(TableName)
For Each idx In tdf.Indexes
If idx.Unique = True And idx.Name <> "PrimaryKey" Then
fnTableHasUniqueIndices = True
Exit For
End If
Next

Set idx = Nothing
Set tdf = Nothing
Set db = Nothing

End Function

Together, these functions will help me develop a generic CopyRecord
function, that will allow me to copy records in tables that contain a
single
PK field, and don't have other unique indices assigned.
Click to expand...


Dale, I wouldn't count on the primary key index being named "PrimaryKey".
Access names the index that way when it creates the index, but there's no
requirement that the primary key be named that. If the index was created in
some other manner -- by code, for example -- it could be named (almost)
anything. Instead, when you are looking at an Index object, test the
object's .Primary property:

If idx.Primary Then
' This index is the primary key.
End If
 
As well as Dirk's suggestion, the primary key can be a multi-field index
(typically a natural key), in which case you may want to know if it's the
first field in the index (where you can use the index to search) or not
(where you can't.)

The DescribeIndexField() function on this page:
http://allenbrowne.com/AppRelReportCode.html
returns an upper case "P" if the field is the primary key (or the first
field in the index), or a lower case "p" if it's a subsequent field in in
the primary key. It returns a "U" if the field is uniquely indexed ("u" for
subsequent field in unique index), or "I" ("i") if the field is indexed at
all.
 
Thanks, guys.

Appreciate your feedback.

Dale

Allen Browne said:
As well as Dirk's suggestion, the primary key can be a multi-field index
(typically a natural key), in which case you may want to know if it's the
first field in the index (where you can use the index to search) or not
(where you can't.)

The DescribeIndexField() function on this page:
http://allenbrowne.com/AppRelReportCode.html
returns an upper case "P" if the field is the primary key (or the first
field in the index), or a lower case "p" if it's a subsequent field in in
the primary key. It returns a "U" if the field is uniquely indexed ("u"
for subsequent field in unique index), or "I" ("i") if the field is
indexed at all.
Click to expand...
 
Back
Top