Remove index

[a7n9]

Hello all,

I'd like to remove every index of a table. I didn't find any property, which can do this. All I could come up with was the indexed field names. Can anyone help me with this.

'remove indexes from a table
Public Sub remove_index()
Dim catcurr As New ADOX.Catalog
'Dim tbl As ADOX.Table
Dim idx As ADOX.Index
Set catcurr.ActiveConnection = CurrentProject.Connection
'tbl.Name = "STU200501_0110052"
With catcurr.Tables("data")
For Each idx In .Indexes
'Debug.Print idx.Name
Debug.Print idx.Properties.Count
'idx
Next
End With
End Sub

 

[Norie]

The following will remove one index from a table:

Public Sub remove_index()
Dim catcurr As New ADOX.Catalog
Dim idx As ADOX.Index
    
    Set catcurr.ActiveConnection = CurrentProject.Connection

    With catcurr.Tables("data")
        For Each idx In .Indexes
    
            .Indexes.Delete idx.Name
        
        Next
    End With
End Sub

I can't see why it won't delete >1. I'll have a play about with it.

Try this

Public Sub remove_index()
Dim catcurr As New ADOX.Catalog
Dim idx As ADOX.Index
    
    Set catcurr.ActiveConnection = CurrentProject.Connection

    With catcurr.Tables("data")
    
        While .Indexes.Count <> 0
        
            Set idx = .Indexes(0)
            
            .Indexes.Delete idx.Name
            
        Wend
        
    End With
End Sub

 

[a7n9]

Worked great. Thanks.