Hi,
the following function will delete an index from the current database (fCurrentApp = true) or another database (fCurrentApp = false and strDatabase contains path + name of the other db)
Code:
Public Function DeleteIndex(fCurrentApp As Boolean, strTable As String, strIndexName As String, Optional strDatabase As String = "") As Integer
On Error GoTo Err_DeleteIndex
'strDatabase: path and name of the access database where you want to work - only used when fCurrentapp = false, when fCurrentapp = true set the parameter to ""
'fCurrentApp: true if the index has to be deleted from the current app, false if it has to be deleted in another access database; in this case fill in strDatabase
'strTable: name of the table where the index has to be deleted
'strIndexname: name of the index
Dim cat As New ADOX.Catalog
Dim cnn As New ADODB.Connection
Dim strConn As String
Dim tdf As ADOX.Table, idx As ADOX.Index
Dim intPos As Integer, intPos2 As Integer
If fCurrentApp Then 'build the index in the current database
Set cnn = CurrentProject.Connection
Else 'build the index in another access file
'build the connection string by getting the current connection string and replace the database path + name with the given path and name in strDatabase
'this way the function works for different versions of Access
strConn = CurrentProject.Connection.ConnectionString
intPos = InStr(strConn, "Data Source=")
intPos2 = InStr(intPos, strConn, ";")
strConn = Left(strConn, intPos - 1) & "Data Source=" & strDatabase & Right(strConn, Len(strConn) - intPos2 + 1)
'open the connection
cnn.ConnectionString = strConn
cnn.Open
End If
Set cat.ActiveConnection = cnn
For Each tdf In cat.Tables
'Debug.Print tdf.Name
If tdf.Name = strTable Then
For Each idx In tdf.Indexes
If idx.Name = strIndexName Then tdf.Indexes.Delete idx.Name
Next idx
End If
Next tdf
cnn.Close
DeleteIndex = 1
Exit_DeleteIndex:
Set cnn = Nothing
Set idx = Nothing
Exit Function
Err_DeleteIndex:
DeleteIndex = -1
Debug.Print Err.Number & ": " & Err.Description
Resume Exit_DeleteIndex
End Function
Beware if you use the code that the references "Microsoft ADO Ext. x.y for DDL and Security" and "Microsoft ActiveX Data Objects x.y Library" are checked in the VBA -> Tools -> References window.
Succes
NG