Deleting indexes using VBA

  • Thread starter Thread starter Luke Bellamy
  • Start date Start date
L

Luke Bellamy

Hi,
I have an access file upgrade routine that adds and deletes fields
accordig to a master schema. When I try to delete a field it sometimes
kicks out with an error saying "Cannot delete field as it effects one or
more
indexes". The fields is certanly indexed so I put some code in to search the
indexes collection of the table and delete it if it exists first.

E.g
Dim strFieldToDelete as String
strFieldToDelete = "IndexedField"

' Delete the index first
Dim myIndex as Index
For each MyIndex in myTable.Indexes
if MyIndex.Name = strFieldToDelete then
myTable.Indexes.Delete strFieldToDelete
end if
Next

' Delete the actual field
myTable.Fields.Delete strFieldToDelete

My problem is that sometimes the field is indexed but it does not find it
in the indexes collection and still gives me the error. I was wondering if
indexes could be checked/deleted a better way than my solution. Are
they held any other area than the Table.Indexes collection.

I can actually debug.print each index name from the table and
the index I am trying to delete is not in there.
Any ideas would be appreciated.

Thankyou
 
You can't assume that the name of the index is the same as the name of the
field.

Each Index object has a Fields collection that represents the fields
included in the index. You'll need to loop through that collection for each
index to determine whether the field's in the index.

The following code hasn't been tested, but it should give you the idea:

Dim MyIndex As DAO.Index
Dim MyField As DAO.Field
Dim strFieldToDelete as String

strFieldToDelete = "IndexedField"

' Delete the index first
Dim myIndex as Index
For each MyIndex in myTable.Indexes
For each MyField in MyIndex.Fields
if MyField.Name = strFieldToDelete then
myTable.Indexes.Delete MyIndex.Name
Exit For
end if
Next
Next

' Delete the actual field
myTable.Fields.Delete strFieldToDelete
 
Back
Top