CODEWITHSUNDEEP
ms-accessvbscript
InfiniteLoop
InfiniteLoop

Reputation: 93

Drop Multiple Index MS Access with VBscript

I would like to create a Sub that does drop some few indexes... Here is what i tried so far:

Sub DropIndex(Database, TableName)

    Set ObjCon3 = CreateObject("ADODB.Connection")
    Set RecSet3 = CreateObject("ADODB.Recordset")


    objCon3.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = "& Database &""

                    ObjCon3.Execute "Drop Index [City] on " & TableName & ", [Company] on " & TableName & ", [Postal Code] on " & TableName & ", [State/Province] on " & TableName & ""


End Sub

Which does not work... Wrong syntax i believe...

else, i think the best way would be to Create an array with the indexes inside.. So i could do:

For X = 0 To Ubound(Index)

Call DropIndex(Database, TableName, Index(x))

Next

But i don't even know where i could get all the indexes in the array. Would i even be able to put a variable in a call?

Upvotes: 0

Views: 189

Answers (1)

Ivan Cachicatari
Ivan Cachicatari

Reputation: 4284

Accoding to MSAccess documentation the drop statement can have only a type of 'drop'.

  DROP {TABLE table | INDEX index ON table | PROCEDURE procedure | VIEW view}

You must change your statement like bellow:

 ObjCon3.Execute "Drop Index [City] on " & TableName 
 ObjCon3.Execute "Drop Index [Company] on " & TableName 
 ObjCon3.Execute "Drop Index [Postal Code] on " & TableName 
 ObjCon3.Execute "Drop Index [State/Province] on " & TableName

Upvotes: 1

Related Questions