How to drop all the indexes of a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I would like to drop all the indexes of a table (or of the full database)
without knowing the number and the name of the indexes.

Could you please help me ?

Regards,

Thomas
 
Loop backwards through the Indexes collection of the TableDef:

Function DeleteIndexes(strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim lngI As Long
Dim lngKt As Long

Set db = DBEngine(0)(0)
Set tdf = db.TableDefs(strTable)
lngKt = tdf.Indexes.Count - 1

If MsgBox("About to delete " & lngKt + 1 & " index(es) from " & strTable
& vbCrLf & "Proceed?", vbYesNo + vbDefaultButton2 + vbExclamation, "Confirm
delete") = vbYes Then
For lngI = lngKt To 0 Step -1
tdf.Indexes.Delete tdf.Indexes(lngI).Name
Next
DeleteIndexes = lngKt & " index(es) deleted."
End If

Set tdf = Nothing
Set db = Nothing
End Function
 
Thank you very much Allen, it's perfect !

Just another question, How can I execute this function automatically ?
I suppose that it is possible to launch it with a parameter (the table name)
from a macro or something like that but I don't see how ?

Thanks in advance !

Thomas
 
I guess you could put something like this into the On Click property of a
command button:
=DeleteIndexes("Table1")

Seems a strange thing to be doing on a regular basis though.
 
Hi Allen,

This is not really what I need, let me explain my process:

To deliver data's to customers, we import data from SQL to a Access DB, then
we make some process on the data's, and then we deliver it.
In the process, the indexes are "changed" but I need to deliver the data
with some specific indexes.

All the process is done with a macro (import data, open Query, ...) I would
like at the end of my process to remove all indexes and create the right one
before the delivery.

This is why I would like to automate the removing of the indexes by a macro
in place of deleting it manually.

Best regards,

Thomas
 
Okay, if you have a macro executing, could you use the RunCode action in the
macro to call the function?
 
Back
Top