Drop Index

[Max]

How do you DROP (delete) and index from a table if the name of the index is
not known?

Thanks,

Max

 

[Allen Browne]

You could loop through the Indexes collection of the TableDef to find the
Name of the one that has the characteristics you want. (By the time you've
done that, it might be easier to Delete from the Indexes than execute a DDL
statement.)

This example shows how to loop through the indexes collection of a table:

Function ShowIndexes(strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
Dim fld As DAO.Field

Set db = DBEngine(0)(0)
Set tdf = db.TableDefs(strTable)
For Each ind In tdf.Indexes
Debug.Print ind.Name, IIf(ind.Primary, "Primary", ""), _
IIf(ind.Foreign, "Foreign", ""), ind.Fields.Count
Debug.Print " Field(s): ";
For Each fld In ind.Fields
Debug.Print fld.Name;
Next
Debug.Print
Next

Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

 

[Max]

Thanks very much Allen, you are a genius.

Max

You could loop through the Indexes collection of the TableDef to find the
Name of the one that has the characteristics you want. (By the time you've
done that, it might be easier to Delete from the Indexes than execute a DDL
statement.)

This example shows how to loop through the indexes collection of a table:

Function ShowIndexes(strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
Dim fld As DAO.Field

Set db = DBEngine(0)(0)
Set tdf = db.TableDefs(strTable)
For Each ind In tdf.Indexes
Debug.Print ind.Name, IIf(ind.Primary, "Primary", ""), _
IIf(ind.Foreign, "Foreign", ""), ind.Fields.Count
Debug.Print " Field(s): ";
For Each fld In ind.Fields
Debug.Print fld.Name;
Next
Debug.Print
Next

Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Function