If your Microsoft Access database is split into front end and back end, how can you easily add an index to a table?
One solution is to use VBA code on the front end to modify table structure on the back end.

The function AddIndexToTable works both if the table is linked or local, because the code checks what kind of table it is.
The subroutine CallAddIndex has sample code to call the function.

Function AddIndexToTable(ByVal TblName As String, IndexName As String, IsPrimary As Boolean, _

                         IsUnique As Boolean, ParamArray FldNames()) As Boolean

Dim Idx As Index

Dim Td As TableDef

Dim DbPath As Variant

Dim Db As Database

Dim FldNum As Integer



On Error Resume Next



'get back end path of linked table

    DbPath = DLookup("Database", "MSysObjects", "Name='" & TblName & "' And Type=6")

    If IsNull(DbPath) Then

        Set Db = CurrentDb 'if local table

    Else

        Set Db = OpenDatabase(DbPath) 'if linked table

        If Err <> 0 Then

            'failed to open back end database

            Exit Function

        End If

        'in case back end has different table name than front end

        TblName = DLookup("ForeignName", "MSysObjects", "Name='" & TblName & "' And Type=6")

    End If



'get table

    Set Td = Db.TableDefs(TblName)

    If Err <> 0 Then

        'failed to get table

        GoTo Done

    End If



With Td

    On Error Resume Next

    Set Idx = .Indexes(IndexName) 'test for existence

    If Err = 0 Then GoTo Done

    

    If Err > 0 Then 'create index

        On Error Resume Next

        Set Idx = .CreateIndex(IndexName)

        With Idx

            For FldNum = 0 To UBound(FldNames)

                .Fields.Append .CreateField(FldNames(FldNum))

                .IgnoreNulls = True

                .Primary = IsPrimary

                .Unique = IsUnique

            Next

        End With

        .Indexes.Append Idx

    End If

    

End With

    

If Err = 0 Then AddIndexToTable = True



Done:

End Function





Sub CallAddIndex()

Dim Result As Boolean



'sample call:

Result = AddIndexToTable("Table1", "MyIndex", False, True, "Field1", "Field2")

Debug.Print Result



'For the FldNames parameter, include one or more field names.

'Field2, Field3, etc. is optional

End Sub