index with dao

[Guest]

I am using access 2000 with dao.

I'm using dbs.Execute to alter table to add a columns (I loop through table
fields to see if columns exists, if not add it).

I then create an index using dbs.execute "create index etc". but don't know
how to check first to see if this index exists otherwise I get errors if run
more than once.

this columns is NOT the primary key, just indexed.

How can I check for the index first?

 

[Allen Browne]

You can list the indexes on a able in DAO with this code:

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

It may be simpler to just create the index, and use error handling.

 

[Guest]

thanks allen, I will take look I don't use this stuff often.

How would I do this with error handling?