"Too many fields defined"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

On a form I have a button for exporting information in a very small query
and I get the above error. I have tried compiling, compacting and then
exporting but I still get the message.

cheers
 
scubadiver said:
On a form I have a button for exporting information in a very small query
and I get the above error. I have tried compiling, compacting and then
exporting but I still get the message.

cheers
Click to expand...

Dear Scubadiver,

As you know: Access keeps an internal count of total number of fields in a
table and has a limit of 255 fields per table. Each time you modify a field
or add a field, this count increases by 1. When you delete a field, Access
does NOT reset this counter. So it's possible for you to have less than 255
fields and still get this error message.

The standard advice "out there" is:

"If your field count is less than 255, just compact the database again which
should reset the internal field count counter."

I ran into this problem many times while changing the field properties of a
table that HAD to have exactly 255 fields. Often, simply compacting the
database wasn't enough. Sometimes I had to make a copy of the table that had
"lost count" of the number of fields. Sometimes I had to make a copy and
then reboot.

Once I had my table perfected and its properties finalized, I was able to
modify the DATA in the table without problems, including importing data from
CSV files, for example. Nor did any queries or reports based on that
255-field table cause the error to re-appear.

I'm not sure if your "too many fields defined" error is coming from your
tables, but I can't imagine how anything but the underlying table could
throw that error. But I wanted to let you and others know what worked for
me.

Good luck, and keep us posted.

Brian
 
I haven't really done much adding or deleting to the table during
development and it has only just occurred.

I didn't realise changing the properties could increase the field size. It
just goes to show how important it is to get the tables correct before doing
anything else.
 
...changing the field properties of a table

FYI: Changing a field's DataType (Numeric to Text) or FieldSize (Long to
Double) will usually cause Access to create a new field, (try to) copy data
to it and delete the old field "under the hood". This deletion goes against
the field counter you describe. You wouldn't necessarily know about this,
and if asked 'have you deleted any fields?" you might say no, but you really
have.

However, I've never seen a case where Compacting didn't reset the counter.
Not saying it couldn't happen, just that Compacting usually sets things
right for me.

HTH,
 
While the others have answered your question and I would appear from
your questions and answers that you may already know this, but it is very
unusual to need anywhere near 255 fields in any one table in a well designed
database.
 
scubadiver said:
I haven't really done much adding or deleting to the table during
development and it has only just occurred.

I didn't realise changing the properties could increase the field size. It
just goes to show how important it is to get the tables correct before
doing
anything else.
Click to expand...
 
Back
Top