I think to drop an index, one would need to know which.
I will usually think that if you need to do design changes at runtime, then there might be flaws in the design.
If you're doing this from VB6, then perhaps a forum dedicated to that might be more relevant?
The below might perhaps be a start, though there's no errorhandling, both table name and column name must be passed ...
Public Sub ChangeIndex(ByVal v_strTable As String, _
ByVal v_strColumn As String)
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim lngCount As Long
Dim strIdx As String
' using a connection to current database
' from VB6 you'll need to open a connection
' with a valid connection string
Set cn = CurrentProject.Connection
' retrieving the indices for the passed
' table name
Set rs = cn.OpenSchema(adSchemaIndexes, _
Array(Empty, Empty, Empty, Empty, v_strTable))
' looping indices, finding index based on passed
' column name
Do While Not .EOF
If ((.Fields("COLUMN_NAME").Value = v_strColumn) And _
(.Fields("UNIQUE").Value = True)) Then
strIdx = .Fields("INDEX_NAME").Value
' if such index is found, drop and recreate
' (howey's code)
If Len(strIdx) Then
' drop existing index
cn.Execute "DROP INDEX " & strIdx & _
" ON [" & v_strTable & "]"
' create new index
cn.Execute "CREATE INDEX " & strIdx & _
" ON [" & v_strTable & "] (" & v_strColumn & ")"