Table has more than 255 fields? But it only has 149...and needs more

[Rashkavar]

First of all, we're not looking for help splitting our oversized 149 field table apart. We tried that, couldn't get our heads around it, and the people who teach Access at the local college couldn't figure it out either. So we're unfortunately stuck with an oversized single table rather than having several smaller, more on topic tables.



We now need to add a few more points of data to our database. When we try to add a new field in design view, we get "Error 3190 - too many fields." According to the help file, this specifically it thinks we're trying to make a table with more than 255 fields (which is an understandable limit; Microsoft was a bit frugal with how they designed the format, so there's a maximum number of fields, and 255 should be more than anyone could reasonably expect to use). The problem is, if you actually count them, there are only 149 fields in the table. Most computers are sufficiently good at math to realize that 149+1=150, not 256.

Does anyone have any idea what could cause this discrepancy?

[RuralGuy]

The 255 is for the life of the design phase of the table so it remembers fields that were added and then removed. Have you tried a Compact and Repair yet? Tables with a 1:1 relationship with each other are rare but not unheard of and not that difficult to achieve.

[KathyL]

You could use a Make Table query to generate a new table with the current table, and it probably will then think it's 149 fields. You will likely loose indexes, but you can set them back up. Another slower process would be to redesign a duplicate new table, and use an Append query to append in all your records. So I really think you can get around this, and get Access back to counting the fields correctly in this one table.

Compact and Repair won't have any impact on your issue.

I've reached the 255 field point... always fun to have to split tables and figure out how you want to change your design... but that's what application designers do.

[orange]

It is rare to see a "normalized" table in a relational database with 140+ fields.
You may wish to review your design and normalize your data before proceeding with your current structure.
As I said, rare, but not impossible.

You may get some ideas from this link
http://www.rogersaccesslibrary.com/T...lationship.zip

[June7]

I just did a test of creating table with 255 fields, then deleted some fields. After running Compact & Repair I could add more fields.

[ItsMe]

I just did a test of creating table with 255 fields, then deleted some fields. After running Compact & Repair I could add more fields.

I hope you did it programmatically.