Record and Field Size Problems

[Guest]

I created a database and related form for a survey. The survey has a number
of "open-ended" questions where people enter free-text responses. I
originally put all survey questions, including all the free-text, in one
table. When we tested it, I found that Access limits the record size and that
by having so many of these fields, all of which I created as Text data types,
the records will exceed the allowed size.

So, I read in the Access specs that Memo fields don't count toward record
size, so I changed all these text fields to Memo fields. This time, I
couldn't even save the table, because I got an error saying there were too
many *fields* in the table, even though I didn't change the number of fields,
and had well under the supposed limit of 255 fields.

Anyone have any suggestions other than breaking the questions into multiple
tables? I was running into a lot more complexity in terms of the
relationships, queries, form, etc. if I try to break the tables up, so if
there's a way to keep the single table, I'd rather do that.

Thanks in advance for any help!

 

[George Nicholson]

First, Compact the database (Tools>Database Utilities>Compact & Repair)

When you "change" a field datatype, you are actually creating a newfield,
copying "old" data into it and deleting the old field.
HOWEVER, the old field still exists behind the scenes and counts against the
255 field limit until you do a compact.

This may not solve your problem, but it's the first thing to do.

 

[Guest]

Thank you, thank you, thank you! It did solve my problem.