Property value too large

[SGNTGH]

Hi,

I have already looked at a few similar threads but nothing's helped so far...

I have a large database for a medical research project (no uni or organisational support for helping with Access, but a supervisor with some experience of Access who has largely helped with everything so far, as well as an Access for Dummies book) using Access 2010 32 bit and keep getting the 'property value too large' error message.

It is a large table (now 234 fields so not quite at limit) with other related tables which I have mostly completed form creation for. I am due to start collecting data tomorrow so am quite keen to not have to do a massive restart and if that's the only solution may have to revert to a giant Excel sheet which will be hideous to look at but at least easier to setup and use!



The problem only arose today after I added 11 more fields to the table (last minute supervisor addition). When I tried to save it initially flashed up asking something about running a check to ensure I wasn't changing existing data (there is no data yet other than some autonumber 1s Access has put into the first form) then flashed up the property value too large message. It does seem to have saved the new fields despite saying it couldn't but it hasn't saved the validation rules (same as existing validation rules in other fields so can't be too large in themselves).

I have tried compact and repair several times but it still keeps flashing up with property value too large every time I try to save it.

I appreciate the table is probably far too big but it was what my supervisor advised me to do (I initially had it split into 2 smaller tables of 100ish fields each but was advised to combine them) so I could create straightforward forms similar to the paper data entry sheets without hundreds of subforms (of which I still have a few). I really don't want to re-split the table as this will mean having to recreate the forms from scratch which I really can't face doing.

Advice would be much appreciated, even if it's dump the lot and revert to Excel...

Thanks!

[Gina Maylone]

Try creating a new DB and import all of your objects into it. You really should have 20-30 fields max (not that you can't have more) but it's what's recommended. You may need to convert to a SQL backend if you indeed need to have the number of fields you describe. Sorry no easy fix.

[Gicu]

Try to add the new fields in a new table and create a query to join the two together. You should be able to then use that query as your record source for the form(s).

Cheers,
Vlad

[Micron]

whenever I read that a table has beyond 75 - 100 fields I figure the chance that the tables are poorly normalized is high. The fact that you all of a sudden had to add 11 fields only strengthens that assumption. That may very well be the thing that drives you back to Excel, which certainly presents its own issues when you try to turn a workbook into a database. There are more size restrictions you need to worry about than just table field count limit, such as their size on disk.

Perhaps the subject of normalization should be explored here?