Too many fields detailed

[heidireb78]

This is most likely a simple thing...

I am just starting to use Access. I have created a database and now it is
won't allow me to add any more fields giving me the "Too many fields defined"
error. The thing is, even if I delete other fields (up to 10 even) it still
won't let me add any back. I am well below the total size of the spreadsheet,
and well under the maximum number of fields (and is quite a simple database).
Any suggestions?

Thanks, Heidi

 

[Jerry Whittle]

Try doing a compact and repair. It's possible exceed the limit by doing
things such as creating fields then deleting them.

Also if you have more than, say, 40 fields in a table, there's a very good
chance that you are building the database incorrectly. If you use Excel, you
might be trying to "commit spreadsheet" which doesn't work well in Access.
Your table could serious normalization problems and just will not work
correctly in a relational database.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.

 

[Jeff Boyce]

Heidi

Access keeps track of how many fields, even if you delete some. Make a
backup copy of the database, then run Compact & Repair to squeeze out those
no-longer-used fields.

NOTE: if you've approached, reached, or exceeded the 250+ limit on fields
in a table, your data design is probably not well-normalized. Who cares?
you ask... You do! You won't get easy or good use of Access'
relationally-oriented features/functions if you don't feed it normalized
data.

If you'd like the newsgroup's feedback on your design, post a description of
why/how you've ended up with so many fields.

A well-normalized relational database design rarely has more than 30 fields
in any one table.

Regards

Jeff Boyce
Microsoft Office/Access MVP