Tags: access, database, delete, drop, index, isnot, microsoft, mysql, oracle, sql, table

Drop Index

On Database » Microsoft Access

3,145 words with 2 Comments; publish: Fri, 30 May 2008 11:28:00 GMT; (25046.88, « »)

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

not known?

Thanks,

Max

All Comments

Leave a comment...

  • 2 Comments
    • 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

      Allen Browne - Microsoft MVP. Perth, Western Australia.

      Tips for Access users - http://allenbrowne.com/tips.html

      Reply to group, rather than allenbrowne at mvps dot org.

      "Max" <max_nospam.ms-access.todaysummary.com.mscm.info> wrote in message

      news:cj0fgi$3p9$1$8302bc10.ms-access.todaysummary.com.news.demon.co.uk...

      > How do you DROP (delete) and index from a table if the name of the index

      > is

      > not known?

      #1; Fri, 30 May 2008 11:29:00 GMT
    • Thanks very much Allen, you are a genius.

      Max

      "Allen Browne" <AllenBrowne.ms-access.todaysummary.com.SeeSig.Invalid> wrote in message

      news:OPSnCKgoEHA.2032.ms-access.todaysummary.com.TK2MSFTNGP10.phx.gbl...

      > 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

      > --

      > Allen Browne - Microsoft MVP. Perth, Western Australia.

      > Tips for Access users - http://allenbrowne.com/tips.html

      > Reply to group, rather than allenbrowne at mvps dot org.

      > "Max" <max_nospam.ms-access.todaysummary.com.mscm.info> wrote in message

      > news:cj0fgi$3p9$1$8302bc10.ms-access.todaysummary.com.news.demon.co.uk...

      >

      #2; Fri, 30 May 2008 11:30:00 GMT