Deleting an index

[Guest]

I have code that deletes an index from a table, then deletes the column containing that index
' Delete the old inde
Set dbs = currentD
dbs.TableDefs.Refres
Set tdf = dbs.TableDefs(tblName
dbs.TableDefs(tblName).Indexes.Refres
Set idxSpace = dbs.TableDefs(tblName).Indexe
For Each idxSearch In dbs.TableDefs(tblName).Indexe
If idxSearch.Name = fieldname The
MsgBox "Deleting " & idxSearch.Nam
sql = "DROP INDEX " & fieldname & " ON " & tblName & ";
currentDB.Execute sq
' Also tried it with the delete method
' currentDB.TableDefs(tblName).Indexes.Delete fieldnam
End I
Nex


dropcolumn
' Delete the old field
sql = "ALTER TABLE [" & tblName & "] DROP COLUMN [
sql = sql & fieldname & "]
qdf.sql = sq
qdf.Execut
' Rename the temporary field to the old field's name
db.TableDefs("[" & tblName & "]").Fields("AlterTempField").Name = fieldnam

When the "ALTER TABLE DROP COLUMN" sql statement executes, I get
Run-time error '3280'
Can't delete a field that is part of an index or is needed by the system

Any ideas? Thanks.

 

[Marshall Barton]

"GreatLakeStuff"

I have code that deletes an index from a table, then deletes the column containing that index:
' Delete the old index
Set dbs = currentDB
dbs.TableDefs.Refresh
Set tdf = dbs.TableDefs(tblName)
dbs.TableDefs(tblName).Indexes.Refresh
Set idxSpace = dbs.TableDefs(tblName).Indexes
For Each idxSearch In dbs.TableDefs(tblName).Indexes
If idxSearch.Name = fieldname Then
MsgBox "Deleting " & idxSearch.Name
sql = "DROP INDEX " & fieldname & " ON " & tblName & ";"

It's more complicated than that. You're trying to delete an
index that is named the same as the field that you want to
delete when you should search for all the indexes where the
field in the index's Fields collection.



Note that there may be more indexes that include the field
than may be apparent at first glance. You may even have to
delete one or more relations before you can delete the
index.

For testing and illumination purposes, try using Debug.Print
instead of just going ahead and deleting things.

Sub ShowIndexeFIelds(strTable As String)
Dim db As Database
Dim ndx As Index
Dim fld As Field

Set db = CurrentDb()
For Each ndx In db.TableDefs(strTable).Indexes
Debug.Print ndx.Name
For Each fld In ndx.Fields
Debug.Print , fld.Name
Next fld
Next ndx

Set fld = Nothing
Set ndx = Nothing
Set db = Nothing
End Sub

 

[TC]

Further to the other reply - and just to be picky - a column does not
"contain an index". An index contains one or more columns.

TC