Unanswered: Error Message 3309 "Property Value too Large"
I analyze very large surveys for non-profit organizations. Some of the surveys have more than 200 fields. Since I understand that there is an upper limit on the number of fields, how do I overcome this upper limit? Can I link two tables of data and go from the form for one table (first half of the survey) to the form for another table (second half of the survey) and transfer the data in the key field from the first table (first half of survey) to the second table (second half of survey)? It was simple to do in Paradox (my old database). Please be specific as I am relatively new at Access.
I have 25 text fields with 15-25 characters each, 20 memo fields and 181 number fields, most of which are integers.
I have reduced the size of each of the text fields by between 5 and 15 characters in hopes of adding more fields but to no avail. I also rebuilt the database per some other suggestions I found on the net.
Total number of fields that I have is 226. I understand the limit is 255. The survey I am working on was revised and I need to add 11 number (integer) fields. The data for the integer fields is mostly whole numbers 1,2,3.
Any help would be greatly appreciated. I would prefer keeping all data in one table if possible.
1. Create additional tables as needed to handle the overflow of additional fields. Just make sure that you have a primary key that is linked between your tables establishing a relationship between them. Then just add them to your form - try it out with the form wizard first to get the hang of it. Since it appears you have more than 1 form it really will not matter from the form side of things as long as you have a key that links the tables together.
2. Since most of the numeric fields are between 1 and 5, you can consolidate those fields using a long integer or a double. You will have to use bitwise manipulations to do this. For example, a long integer is 4 bytes which equals 32 bits of storage capacity. Since the largest value, 5, only consumes 3 bits of space, you can consolidate 10 fields into 1. Compressing your total fields from 226 to 64. This would require you to programmatically handle the conversion from the database to the form.
Normally, when I hear someone talk about a scenario like this one - repeating groups is the solution and just normalize your database - but I think with what you are trying to accomplish with access and the type of data you are working with, the top 2 solutions are a better path.