Saving table, get error "Property value too large" MS Access 2003 SP3

[pickymiss]

Can't find this in any previous threads, some similar, but none address this exact question. I have used MS Access for a while, but generally feel like I can make great looking user interfaces, but lack technological know-how in many cases. Sorry this is so long, but I wanted to give as much info as possible. If someone knows which thing is the problem and can direct me to a thread, that would be great. I have looked up each thing individually, but haven't solved anything.

I'm using MS Access 2003, SP3.

I (also) have a research study that needs a vehicle to capture data and I've used MS Access. There are very few rows (<50), but many data points (each unique).

I have a 234 column table. There is no duplicate data - registration form, tons of answers to capture. (MS Access may not be the best data capture vehicle, but it does what I need to do better than anything else I can think of – suggestions welcome. Seems like lots of people with research studies to capture use MSA… and have lots of problems with too many data points.)

Initially I had this information split into five different tables, but it seemed easier to consolidate the information in one. Maybe not… and reverting may be easier and quicker than trying to figure this out.

I am getting the error "Property value too large" when trying to save the table.

Here are all my dirty little secrets…things I have checked and things I’ve done. I’ve already worn through my “Bang Head Here” sign so please be gentle in telling me why each of these is “the wrong way” to do this…

-- Only 1 field is indexed… it is the primary key also.

-- 234 is smaller than the limit for number of columns (Types are 78 Yes/No, 94 Text, 31 Double, 3 Date/Time, 12 Long Integer, and 16 Memo).

-- None of the field names exceed the max length of 64 (35 is longest - yes, also probably ridiculous). Total character count of field names is 4,320 – could this be the same as the next issue?

-- If the records can't be more than 2,000 characters and that count EXCLUDES Memo and OLE objects (found this in another forum's answers) I should still be ok - current largest character count is 1477. (If it were to include Memo fields, wouldn’t everyone be over 2k?)

-- I chopped down the descriptions of the fields (3rd column in table design view) most are 3 characters (total characters all descriptions = 1,251)

-- I did hear that Lookups are evil… (read a posting on mvps.org... /access/lookupfields.htm) In my table there are 55 fields taking their data from 23 source tables. Combo Box, Table/Query... Maybe there was a better way to have done this. People can’t type and there are text answers that need to be identical across all records. The text answer chosen via dropdown is stored in the table, not a number (table view shows text as does UI). RowSource tables had only one column – the text to be entered. Example: Main table field: “Feelings,” in the RowSource “T_List_Feelings” there is one column named “Feelings.” There are three choices: Terrific, Ok, Horrible. When entering data in the form, those three words are in the dropdown and when one is chosen it goes into the table. I am not sure if it continues to look up the value after that… or if the data is just there. I did end up adding a number column to the T_List_Feelings table, but it doesn’t have any relationship with the big table… it is just there in the list table.

-- Does changing the format of the table (i.e. font, font size/color) have anything to do with this? Seems strange if it would.

I know those of you in the know have been shaking your heads the whole time you’ve been reading. I am sorry if you are experiencing neck pain.

Reverting to multiple tables may be the solution in this case, but I will be having this type of issue many times in the future, so I’d like to know which things I am doing wrong and whether there is a better way to do some of this. Again, I am open to suggestions for better tools to do this. I need to link different data about the research subjects together and report on it, so MSA seemed the best choice.

I know the table is really wide… I’d love to make a tall, skinny table, but can’t capture all the data points that way.

Any information would be appreciated. I'll be back slogging through this on Monday morning.

Thanks...

 

[dhookom]

As a fellow Access MVP (John Vinson) often states "fields are expensive and records are cheap". You have done lots of expensive work .

I'm not sure why you "can't capture all the data points" using a solution like At Your Survey. Even if you couldn't capture all the data, I think you could capture a vast majority of your data points in a normalized table structure.

There are limits to the number of fields and characters stored per record. You may hit these limits earlier than expected if you have modified your table structures without compacting your database.

I was part of the group that crafted the Evil of Lookup fields and personally never use them. I use combo boxes on forms to create a friendly, functional user interface.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Seems like lots of people with research studies to capture use MSA... and have lots of problems with too many data points.)

The problem is not to many data points, but poor understanding of relational database design. You should be able to capture 1000s of "data points" if properly designed. I have built 100s of technical databases/applications and rarely have had a single table with more than 20 fields.

I would look at At Your Survey to see an example of a well designed database.

You are banging up against the field limit of 234. There may be hidden columns that will free up if you compact and repair. But I would not waste my time trying to fix it. I would normalize/redesign the database.

From what you describe, I would envision a handful of tables with no more than 10 fields per table.

Now people will argue that a bunch of questions constructed as fields is a normal design, and it can be debated. However, in praticallity a bunch of records representing an answer to a related table of questions related to a table of respondents is far more flexible and far easier to work with and manage.

 

[pickymiss]

Thanks... I have compacted and repaired the database many times (I guess it holds on to “ghosts of fields past” until that is done). I am going to look at using combo boxes.

Maybe I shouldn’t have used the word “data points.” What I meant to indicate when I said it was that I have a study with a form that has ~230 questions. Each question has a different answer to capture. There is no overlap. I will have very few records, but as far as I can see, I need ~230 fields. If there is a way to do it, I would be glad to do it “the right way” in future instances. I don’t know exactly what this sentence means to me:

“a bunch of records representing an answer to a related table of questions related to a table of respondents is far more flexible and far easier to work with and manage”

I am looking question by question and trying to figure out which could have answers in related tables linked to related respondents. I can see only one right away… Do you have: strawberries? If yes, how many?; blueberries? If yes, how many?; bananas? If yes, how many?... It isn’t really asking about fruit, but I see the “what” even if I don’t know the “how.” It would take away the need for some fields. Most of the questions are nothing like that, so I’d still be wide and shallow. I do have other thin long tables in there that are much more like what you are talking about.

Getting down to 10 or 20 fields in a table sounds great, but even if I were an evil genius I don’t think it could be that small. Not that either of you is an evil genius. ;-)

“Now people will argue that a bunch of questions constructed as fields is a normal design, and it can be debated.”

Maybe we should do a study.

There isn’t that much data left to input (small group of participants and relatively short study duration). It is heavy on text and not much to analyze. Redesigning this particular database would take longer than it is worth. I believe this time I’ll just re-split the table and go on in the “abnormal” way.

In the future I would love to tame the tiger (just on principle)… and clearly I do need know how to build a better tiger. I’ll take a look at the solutions in “At Your Survey” and see if I can figure anything out. Right now I feel like I’d have the paw coming out of its ear. Maybe I'll use this DB as my learning lab (after it is no longer used).

Thanks for your time and suggestions...

Tek-Tips answers keep me out of that white jacket with the extra long sleeves... Thanks

 

[MajP]

Bottom line is most survey databases have a table of questions. Each question is a record not a field. Then they have a table of responses to the question and each record is a response to a question.

So in your design you have something like

respondentID
question1
...
question230

In most survey databases the responses are not fields they are records

tblResponses
respondentID
questionID
respondentValue

tblQuestions
questionID
questionDescription
responsetype

the data in tbl questions is something like
1 Do you have strawberries? Yes/NO
2 How many strwabwerries? Numeric
3 Do you have blueberries? Yes/No
4 How many blueberries? Numeric
....
230 questions Text

So my response table data looks like for respondent 1
1 1 yes
1 2 5
1 3 No
1 4 0
....
1 230 Average

Now this is overly simplified because I put yes no, numeric, and text answers in the same field, but it provides the general idea.

And normally your response type would be something more like
Likert_goodToBad which would correspond to another table of choices like
poor
fair
good
excellent
l
Likert_agreeToDisagree
strongly Diagree
Disagree
neutral

Then the user gets the appropriate scale for the the type of question.

In my opinion survey databases are very difficult to build, and require a very good understanding of database normalization in order to build a good flexible database.
Unfortunately, people tend to tackle these like they would a spreadsheet. You can very easily and quickly build a survey database that kind of works, but they are inflexible and limited.

Getting down to 10 or 20 fields in a table sounds great, but even if I were an evil genius I don't think it could be that small.

I have a survey database that is about as robust as it gets. Can handle any number of questions, any type of scale or response, calculates metrics, allows of online multiuser dynamic surveys, calculate numerous metrics, can be reused to apply to other systems, etc. Lots of tables, not one table over 15 fields. This is basically a commercial level application, used by the government.

Looking at AtYourSurvey, I think the most fields in a table is 18. And this is a very robust application. If you can digest what Duane did you will have a very good understanding of proper database design.

 

[dhookom]

Building a survey application should be the responsibility of a developer. Building a survey should be the responsibility of an end user.

Duane
Hook'D on Access
MS Access MVP