Ex4: VBA - Create Indexes using DAO

Create primary key, foreign key, and unique indexes; single- and multi-field using DAO

Option Compare Database
Option Explicit

'Constants for examining how a field is indexed.
Private Const intcIndexNone As Integer = 0
Private Const intcIndexGeneral As Integer = 1
Private Const intcIndexUnique As Integer = 3
Private Const intcIndexPrimary As Integer = 7

Function CreateIndexesDAO()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim ind As DAO.Index
    
    'Initialize
    Set db = CurrentDb()
    Set tdf = db.TableDefs("tblDaoContractor")
    
    '1. Primary key index.
    Set ind = tdf.CreateIndex("PrimaryKey")
    With ind
        .Fields.Append .CreateField("ContractorID")
        .Unique = False
        .Primary = True
    End With
    tdf.Indexes.Append ind
    
    '2. Single-field index.
    Set ind = tdf.CreateIndex("Inactive")
    ind.Fields.Append ind.CreateField("Inactive")
    tdf.Indexes.Append ind
    
    '3. Multi-field index.
    Set ind = tdf.CreateIndex("FullName")
    With ind
        .Fields.Append .CreateField("Surname")
        .Fields.Append .CreateField("FirstName")
    End With
    tdf.Indexes.Append ind
    
    'Refresh the display of this collection.
    tdf.Indexes.Refresh
    
    'Clean up
    Set ind = Nothing
    Set tdf = Nothing
    Set db = Nothing
    Debug.Print "tblDaoContractor indexes created."
End Function