We use the below code to delete an index then delete the field. If the field has an index, VBA will not be allowed to delete it. This works great if there is an index but will error if there is not an index. How can we detect with VBA if an index exists before we attempt to delete it?

Case Is = "IVAULDLCode"     'ep added on 6.6.15
tdf.Indexes.Delete ("IVAULDLCode")
tdf.fields.Delete ("IVAULDLCode")

---

Reply from Alex Hedley:

On Error Resume Next, only joking, that's the lazy way out.

Loop indexes in a Table, change "Table1" to your Table name.
You could make this a Function with TableName as parameter and Index Name to check for and return a Bool.

Dim db As DAO.Database
Dim tbldf As DAO.TableDef
Dim idx As index

Set db = CurrentDb
Set tbldf = db.TableDefs("Table1")

For Each idx In tbldf.Indexes
  MsgBox idx.Name
Next idx

Set db = Nothing
Set tbldf = Nothing

Allen Browne has a Function (IndexOnField) for this:

Private Function IndexOnField(tdf As DAO.TableDef, fld As DAO.Field) _
As Integer
    'Purpose:   Indicate if there is a single-field index _
    '               on this field in this table.
    'Return:    The constant indicating the strongest type.
    Dim ind As DAO.Index
    Dim intReturn As Integer
    
    intReturn = intcIndexNone
    
    For Each ind In tdf.Indexes
        If ind.Fields.Count = 1 Then
            If ind.Fields(0).Name = fld.Name Then
                If ind.Primary Then
                    intReturn = (intReturn Or intcIndexPrimary)
                ElseIf ind.Unique Then
                    intReturn = (intReturn Or intcIndexUnique)
                Else
                    intReturn = (intReturn Or intcIndexGeneral)
                End If
            End If
        End If
    Next
    
    'Clean up
    Set ind = Nothing
    IndexOnField = intReturn
End Function