Remove Index From MDB programmatically

The following VBA code is used to remove the index from MS Access table programmatically.We need to assign the database to Database type variable. Then get the table value into tabledef collection. Then using delete command we can remove the indexes.

Sub RemoveIndexFromMSAccessDB()

'Declaration of Variables
Dim indexName As String
Dim AccessDb As Database
Dim Td As TableDef

'Error Handler
On Error Go to ErrHandler

'Assign the database
Set AccessDb = OpenDatabase("C:\Test.MDB")


If Err.Number <> 0 Then

'failed to open access database

MsgBox "Error " & Err.Description & "Err No " & Err

Exit Sub
End If

'Assign Index

indexName = "MyIndex"

'get table

Set Td = AccessDb.TableDefs("MyTable1")

If Err.Number <> 0 Then

'failed to get table
GoTo ErrHandler

End If

With Td

On Error Resume Next
.Indexes.Delete IndexName

End With



ErrHandler:

MsgBox "Error " & Err.Description & "Err No " & Err

End Sub