Deleting Indexes

aziz rasul

Active member
Local time
Today, 00:09
Joined
Jun 26, 2000
Messages
1,935
Why does this code work: -

Code:
    For i = 0 To DBEngine(0)(0).TableDefs("Sheet1").Indexes.Count - 1
        IndexName = DBEngine(0)(0).TableDefs("Sheet1").Indexes(i).Name
        CurrentDb.Execute "DROP INDEX [" & IndexName & "] ON Sheet1;"
    Next i

but this doesn't i.e. it deletes the first of two indexes but errors when it should delete the second index but IndexName still holds the name of the deleted index and comes up with an error item not in collection.

Code:
    For i = 0 To CurrentDb.TableDefs("Sheet1").Indexes.Count - 1
        IndexName = CurrentDb.TableDefs("Sheet1").Indexes(i).Name
        CurrentDb.Execute "DROP INDEX [" & IndexName & "] ON Sheet1;"
    Next i
 
Hi -

Try reversing your count procedure, i.e.
Code:
For i = DBEngine(0)(0).TableDefs("Sheet1").Indexes.Count - 1 to 0
   IndexName = DBEngine(0)(0).TableDefs("Sheet1").Indexes(i).Name       
   CurrentDb.Execute "DROP INDEX [" & IndexName & "] ON Sheet1;" 
Next i

Bob
 
The DBEngine code is fine, it's the CurrentDb equivalent that is not working. However I did try using your code with CurrentDb but it just skipped the For Next routine.
 
Aziz,

Bob's got the idea, but you need to have --> Step -1

for the loop.

If you go "forward", by the time you get to the 2nd index ... it is now the 1st!

Wayne
 
Thanks Wayne. That did the trick. Does seem strange it doesn't work the other more conventional way. U learn something new all the time.
 

Users who are viewing this thread

Back
Top Bottom