Determine if a field is Indexed

Question

Thanks for taking the time to read my question.

I'm iterating through all fields in a table to get the field name, data type, required, etc.

I can't figure out how to determine if the field is indexed and how it's indexed.

"Yes (No Duplicates)"
"Yes (Duplicates OK)"
"No"

Is there an easy way to do this?

Thanks,
Brad

Answer 1

If you're using DAO, you have to loop through the Indexes property of the TableDef object.

Each Index has a Fields collection that lists the fields contained in the index - an index can be on one field or on a combination of two or more fields. This also means that a field can be part of multiple indexes, of which some could allow duplicates and others not.

Here is a very rough example:

Sub Test()
    Const strTable = "tblEmployees" ' Table name
    Const strField = "strLastName" ' Field name
    Dim strMsg As String
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim idx As DAO.Index
    Dim fld As DAO.Field
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTable)
    For Each idx In tdf.Indexes
        For Each fld In idx.Fields
            If fld.Name = strField Then
                strMsg = "The field " & strField & _
                    " is part of the index " & idx.Name & vbCrLf
                If idx.Unique Then
                    strMsg = strMsg & "This index is unique"
                Else
                    strMsg = strMsg & "This index is not unique"
                End If
            End If
            MsgBox strMsg, vbInformation
        Next fld
    Next idx
End Sub

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Thanks Hans!

I actually found something similar on line, struggled with it but I think I have ended up with what you suggested... I must be learning :-)

GetFieldIndex does what you have posted and finds out if a field is index and unique.

GetIndex checks to see if the index is a group of fields and if it's unique.

Please let me know if you see anything wrong.

Function GetIndex(tblName As String) As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field
Dim indxFields As String
Dim strField As String
 
Set db = CurrentDb()
Set tdf = db.TableDefs(tblName)
Set rst = db.OpenRecordset(tblName, dbOpenTable)
 
If GetIndex = "" Then
    ' List values for each index in the collection.
    For Each idx In tdf.Indexes
      ' The index object contains a collection of fields,
      ' one for each field the index contains.
        'Debug.Print idx.Fields.Count
        If idx.Fields.Count > 1 Then
            For Each fld In idx.Fields
              If indxFields = "" Then
                indxFields = fld.Name
              Else
                indxFields = indxFields & "," & fld.Name
              End If
              
            Next fld
            If Left(GetIndex, 5) = "INDEX" Then
                GetIndex = GetIndex & vbCrLf & "CREATE " & IIf(idx.Unique = True, "UNIQUE ", "") & "INDEX " & "[" & idx.Name & "]" & vbCrLf & "ON " & tblName & " (" & indxFields & ")"
            Else
                GetIndex = "CREATE " & IIf(idx.Unique = True, "UNIQUE ", "") & "INDEX " & "[" & idx.Name & "]" & vbCrLf & "ON " & tblName & " (" & indxFields & ")"
            End If
            indxFields = ""
        End If
    Next idx
    If GetIndex <> "" Then
        GetIndex = GetIndex & ";"
        'Debug.Print GetIndex
    End If
End If

End Function


Function GetFieldIndex(tblName As String, fldName As String) As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field
 
Dim strField As String
 
Set db = CurrentDb()
Set tdf = db.TableDefs(tblName)
Set rst = db.OpenRecordset(tblName, dbOpenTable)
 

For Each idx In tdf.Indexes
    If idx.Name = fldName Then
        If idx.Unique = True Then
            GetFieldIndex = " NOT NULL UNIQUE"
        Else
            GetFieldIndex = " NOT NULL"
        End If
        Exit For
    End If
Next idx

End Function

Thanks,
Brad

Answer 3

If a field is indexed, that means it is included in an Index in the tabledef's Indexes collection.  This is made more complicated by the fact the the same field may be indexed multiple times, sometimes allowing duplicates, sometimes not.  Access determines what to display in a table's property sheet based on the most restrictive index.  Also, a field may participate in compound indexes that include multiple fields, but that won't count for whether or not the field is indexed by itself.

I believe the function below, which I just threw together, might serve your purpose:

Function HowIsFieldIndexed(tableToCheck As Variant, fieldName As String) As String

    ' Return one of the following, depending on whether and how the given field
    ' is indexed in the table:
    '
    '   "Yes (No Duplicates)"
    '   "Yes (Duplicates OK)"
    '   "No"
    '
    ' Arguments:
    '
    '   tableToCheck - this may be either the (string) name of the table to look in,
    '                  or a DAO TableDef object representing that table.
    '
    '   fieldName    - the (string) name of the field.
    '
    ' Note: if the field doesn't exist in the table at all, this function will just
    '   return "No", meaning the field is not indexed, and won't raise an error.
    '   It could easily be modified to verify that the field exists in the table.

    Dim db          As DAO.Database
    Dim tdf         As DAO.TableDef
    Dim ix          As DAO.Index
    Dim isIndexed   As Boolean
    Dim isUnique    As Boolean
    
    Select Case TypeName(tableToCheck)
        Case "TableDef"
            Set tdf = tableToCheck
        Case "String"
            Set db = CurrentDb
            Set tdf = db.TableDefs(tableToCheck)
        Case Else
            Err.Raise 5
    End Select

    With tdf
    
        For Each ix In tdf.Indexes
            If ix.Fields.Count = 1 And ix.Fields(0).name = fieldName Then
                isIndexed = True
                If ix.Unique Then
                    isUnique = True
                End If
            End If
        Next ix
        
    End With

    If isIndexed Then
        If isUnique Then
            HowIsFieldIndexed = "Yes (No Duplicates)"
        Else
            HowIsFieldIndexed = "Yes (Duplicates OK)"
        End If
    Else
        HowIsFieldIndexed = "No"
    End If
    
    Set tdf = Nothing
    Set db = Nothing

End Function

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 4

Hi Brad,

Thanks for sharing the solution.

If there is no other issues, I would suggest you mark the helpful reply as answer which is the way to close a thread here.

Regards,

Tony

---

Help each other