Error message: "Too many fields defined"

[MarkGardner]

I have a table with 22 fields defined. When I attempt to add one more field and then I try to save, I get the error message:
"Too many fields defined. " I understood that the maximum number of fields allowable was 255.

In the past, when I encountered this problem, I deleted multiple fields no longer needed so that I could add just one more field that I needed, but that did not work.

I even attempted to reduce the size of the fields remaining to see if that would help, but to no avail. I still got the error message even though the total number of fields was less than I started with. About half of my 22 fields have character size of 4. The rest are numeric fields.

The HELP button has offered a solution to alter some settings, but it gives a serious and scary warning with the solution that messing with those settings may cause the operating system to fail, so I do not want to even go there. I never did get a solution in the past. I am now faced with the problem again. Any suggestions?

Thanks, Mark Gardner

 

[Banana]

Out of curiosity, how many tables and fields you have in total?

I know there's a limit on numbers of objects you can have in a single .mdb file and wonder if this may be why you are having that problem.

Another way to test is to create a brand new table that's identical to that table you are having problem and see if the problem recur or not.

 

[WayneRyan]

Mark,

Tables, like forms have a maximum number of elements they'll support. They
also tend to "remember" former elements even though they have been
removed from their definition.

If you copy the table to a new one, you'll be able to resume operations.
If the table has relationships, it will become a bit more work to just replace
the table.

If you are programatically adding/removing controls to a form, or adding/removing
columns to a table, this type of thing will crop up. It's best to avoid doing
that in the first place.

I don't think I worded this too well, but I hope it's somewhat clear.

Wayne

 

[Mike375]

Have you tried compacting and then adding the other fields.

If you delete fields then until it is compacted it is as if they are still there. So if you started with a table that had 30 fields then deleted 10 and add back 10 then until compacting Access would think there are 40 fields. So if you do this enough times without compacting then Access will think you have hit 255 fields.

 

[MarkGardner]

Thanks everybody for your assistance, inquiry and suggestions. I have not counted the number of tables, but there are a whole ton of them and have added many tables since the last time that had the problem occurred several months ago.
I did take the suggestion and made a copy of the table. It added without any problems. I then went to your next step and did the same as I did when the problem popped up today. I added the field that I wanted to add with the 4 character field size. I was allowed to successfully save without any problems.

Now the question remains. What exactly does the error message mean and why did it work when I created a copy and do the exact same thing? Anyway, I am back in business. Thanks again everybody!
Mark

 

[boblarson]

sounds like you may need to reset things a bit by importing all of your objects into a new, blank mdb (or accdb file if using 2007) so that all of the previously deleted items and such don't have an effect as it is all reset if you import the objects into a new database file.