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
http://ms-access.itags.org/q_ms-access-database_62642.html
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.itags.org.mscm.info> wrote in message
news:cj0fgi$3p9$1$8302bc10.ms-access.itags.org.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.itags.org.SeeSig.Invalid> wrote in message
news:OPSnCKgoEHA.2032.ms-access.itags.org.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.itags.org.mscm.info> wrote in message
> news:cj0fgi$3p9$1$8302bc10.ms-access.itags.org.news.demon.co.uk...
>
#2; Fri, 30 May 2008 11:30:00 GMT