Max Field limit

[Leif]

I created table with a mix of number, date, and text fields. The table
contains 172 fields. I was able to save the table and add records to the
table.

Now I'm trying to change the property of one of the text fields, from a
maximum of 255 characters to 100 characters. When I go to save the table I'm
told I have too many fields, and I cannot save my change.

Is there a field limit? And if so why accept 172 fields, but not allow me
to make any changes? It does ask me if it is OK to lose data, and I reply
with Yes.

 

[Douglas J. Steele]

The field limit is 255. However, making design changes to a field can cause
Access to create a new column and delete an existing one behind the scenes.
Try compacting the database to make sure the field count is correct.

BTW, there really is no need to reduce the field size. Access only takes a
much space as is required for the text being stored.

 

[Jeff Boyce]

Leif

A table with 172 fields sounds more like ... a spreadsheet! You may not be
getting the best out of Access if you feed it "sheet" data.

If you care to post back a description of what you are putting in all those
fields, the folks here in the newsgroup may be able to offer alternate
approaches.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Klatuu]

In addition to the 255 fields limit, there is also a 4000 character per
record limit. That may be what you are exceeding.

As Jeff said, a table with 172 fields is an indication of a database design
that needs review and rethinking.

 

[Evi]

Please don't waste your time trying to make it work as it is, it is almost
certain that your design is wrong and, when you realize the full
implications of what this means, you will want to redesign it and will have
to scrap all the work you have done.
Evi