Deleting Indexes from a Table : Index « Access « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Access
3. Index

Deleting Indexes from a Table

 
Sub Delete_Indexes()
   Dim conn As New ADODB.Connection
   Dim cat As New ADOX.Catalog
   Dim myTable As New ADOX.Table
   Dim idx As New ADOX.Index
   Dim count As Integer

   With conn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open "Data Source=" & CurrentProject.Path & _
          "\mydb.mdb"
   End With

   cat.ActiveConnection = conn
Setup:
   Set myTable = cat.Tables("Employees")
   Debug.Print myTable.Indexes.count
   For Each idx In myTable.Indexes
       If idx.PrimaryKey <> True Then
         myTable.Indexes.Delete (idx.Name)
         GoTo Setup
       End If
   Next idx

   conn.Close
   Set conn = Nothing
   MsgBox "All Indexes but Primary Key were deleted."
End Sub

 

Related examples in the same category