primary key

  • Thread starter Thread starter mcnews
  • Start date Start date
M

mcnews

is there some way to determine what the primary key on a table is via
VB code or other method during runtime?
tia,
mcnewsxp
 
this seems to work:

Function GetPrimaryKey(ByVal strTable As String) As String

Dim ind As Index
Dim tdf As dao.TableDef
Dim dbs As dao.Database
Dim fld As dao.Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)

For Each ind In tdf.Indexes 'loop through the indexes
If ind.Primary = True Then 'check if it's primary
For Each fld In ind.Fields
GetPrimaryKey = fld.Name
Next
Exit For
End If
Next

End Function
 
A primary key is simply an index, and an index can contain up to ten
separate fields. The following code will go through all the tables in your
application and print the details for the primary key of each one:

Sub PrimaryKeyDetails()
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim idxCurr As DAO.Index
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
For Each idxCurr In tdfCurr.Indexes
If idxCurr.Primary = True Then
Debug.Print "Table " & tdfCurr.Name & _
" has Primary Key " & idxCurr.Name & _
" which contains the following fields:"
For Each fldCurr In idxCurr.Fields
Debug.Print " " & fldCurr.Name
Next fldCurr
Debug.Print ""
End If
Next idxCurr
End If
Next tdfCurr
Set dbCurr = Nothing

End Sub
 
i didn't think about compound keys or spaces in names.

Function GetPrimaryKey(ByVal strTable As String) As String

Dim ind As DAO.Index
Dim tdf As DAO.TableDef
Dim dbs As DAO.Database
Dim fld As DAO.Field
Dim strPrimaryKey As String

Set dbs = CurrentDb
strTable = "[" & strTable & "]"
Set tdf = dbs.TableDefs(strTable) 'set a reference to your table
strTable = strTable & "."
For Each ind In tdf.Indexes 'loop through the indexes
If ind.Primary = True Then 'check if it's primary
For Each fld In ind.Fields
'strPrimaryKey = strPrimaryKey & fld.Name & " + "
strPrimaryKey = strPrimaryKey & strTable & "[" & fld.Name & "]" &
" + "
Next
Exit For 'exit if primary key found since there is only one for a
Table
End If
Next
strPrimaryKey = left(strPrimaryKey, Len(strPrimaryKey) - 3)
GetPrimaryKey = strPrimaryKey

End Function
 
Back
Top