Can't delete fields

Hello all,

I'm having a problem deleting fields; I am using tabledefs in VBA to delete
fields from a temporary table at run-time, but I keep getting
the error message:

"Can't delete a field that is part of an index or is needed by the system.
(Error 3280)"

The field is always the same field; it is not indexed or a key, nor does it
have any relations to other tables. I'm really stumped as to why it's doing
this. Are there any other reasons why this message will appear? My code is
below.

For intOutCounter = tdf.Fields.Count - 1 To 0 Step -1
For intCounter = 1 To 10
boolExistingField = False
If tdf.Fields(intOutCounter).Name = strStep(intStepNumber,_ _
intCounter) Then
boolExistingField = True
Exit For
End If
Next intCounter
If Not boolExistingField Then
tdf.Fields.Delete tdf.Fields(intOutCounter).Name
End If
Next intOutCounter

The error always happens around tdf.Fields(30)
TIA for any help you all can offer, because I'm lost.

Nick

--
Message posted via http://www.accessmonster.com

re: Can't delete fields

Nick via AccessMonster.com wrote:[color=blue]
> Hello all,
>
> I'm having a problem deleting fields; I am using tabledefs in VBA to delete
> fields from a temporary table at run-time, but I keep getting
> the error message:
>
> "Can't delete a field that is part of an index or is needed by the system.
> (Error 3280)"
>
> The field is always the same field; it is not indexed or a key, nor does it
> have any relations to other tables. I'm really stumped as to why it's doing
> this. Are there any other reasons why this message will appear? My code is
> below.
>
> For intOutCounter = tdf.Fields.Count - 1 To 0 Step -1
> For intCounter = 1 To 10
> boolExistingField = False
> If tdf.Fields(intOutCounter).Name = strStep(intStepNumber,_ _
> intCounter) Then
> boolExistingField = True
> Exit For
> End If
> Next intCounter
> If Not boolExistingField Then
> tdf.Fields.Delete tdf.Fields(intOutCounter).Name
> End If
> Next intOutCounter
>
> The error always happens around tdf.Fields(30)
> TIA for any help you all can offer, because I'm lost.[/color]

Try using Tools, Relationships (I assume you have) and ensure there isn't
any relationship. Then check the system table "MSysRelationships" to confirm
that there isn't a record in the table that (for some reason) is not being
displayed in the UI.

--
'---------------
'John Mishefske
'---------------

re: Can't delete fields

First off, thanks for the reply.

I tried your suggestions, but neither showed any relations to the field that
can't be deleted.

Is it possible that the field name could be a problem? This might not make
sense but it's about the last thing that I can think of that could remotely
cause this problem. Would the field name "Harmonize Code" cause a problem?
I wouldn't think so, but it's always Harmonize Code causing the problem -
even when I delete and re-create the field.

Thanks,
Nick

--
Message posted via http://www.accessmonster.com