"Property value is too large."

Hello,
I designed a table with 225 fields and saved without problem. Right now, I
am creating a form using that table. However, when I want to make some
changes in the table (e.g. change a text field to a Yes/No field), I got
error messages of "Property value is too large"; or sometimes it shows "Too
many fields..."
So I cannot make any changes for the existing table. Could anyone give me
some suggestions on this issue?
Thanks in advance!

On Tue, 17 Feb 2009 13:04:01 -0800, Vincdc
wrote:

Hello,
I designed a table with 225 fields and saved without problem. Right now, I
am creating a form using that table. However, when I want to make some
changes in the table (e.g. change a text field to a Yes/No field), I got
error messages of "Property value is too large"; or sometimes it shows "Too
many fields..."
So I cannot make any changes for the existing table. Could anyone give me
some suggestions on this issue?
Thanks in advance!

A Yes/No field is stored as -1 for True/Yes/checked, 0 for False/No/unchecked.
Changing a text field to a Yes/No field will probably NOT work.

And, every time you change a property of a field, one of the 255 "slots" for
fields gets used up. You must Compact and Repair the database to recover them.

I should note that a 225 field table IS INCORRECTLY DESIGNED. 30 fields is a
wide table; 100 fields is a huge table; 225 fields is a spreadsheet
masquerading as a table. I'm sure you have at least one, probably several, one
to many relationships embedded in this monster! If it's a questionnaire,
consider using a normalized design such as

Duane Hookum's "At Your Survey":
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 02000'

or

Roger Carlson's Training Registration database:
http://www.rogersaccesslibrary.com/d...gistration.mdb
--

John W. Vinson [MVP]

Hi John,
Yes, this is a very large questionnaire. I have gone through the
normalization process and moved redundant fields into other tables. Right
now, fields in this main table are "one-to-one relationship" and there is no
need to separate them into different tables, unless you suggest to do that to
improve performance.
I also tried to compact the database and it does not work either. I still
got the error message. However, I found that Access saved the changes I made.
It is kind of weird, no idea on it.
Thanks!


"John W. Vinson" wrote:

On Tue, 17 Feb 2009 13:04:01 -0800, Vincdc
wrote:

Hello,
I designed a table with 225 fields and saved without problem. Right now, I
am creating a form using that table. However, when I want to make some
changes in the table (e.g. change a text field to a Yes/No field), I got
error messages of "Property value is too large"; or sometimes it shows "Too
many fields..."
So I cannot make any changes for the existing table. Could anyone give me
some suggestions on this issue?
Thanks in advance!

A Yes/No field is stored as -1 for True/Yes/checked, 0 for False/No/unchecked.
Changing a text field to a Yes/No field will probably NOT work.

And, every time you change a property of a field, one of the 255 "slots" for
fields gets used up. You must Compact and Repair the database to recover them.

I should note that a 225 field table IS INCORRECTLY DESIGNED. 30 fields is a
wide table; 100 fields is a huge table; 225 fields is a spreadsheet
masquerading as a table. I'm sure you have at least one, probably several, one
to many relationships embedded in this monster! If it's a questionnaire,
consider using a normalized design such as

Duane Hookum's "At Your Survey":
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 02000'

or

Roger Carlson's Training Registration database:
http://www.rogersaccesslibrary.com/d...gistration.mdb
--

John W. Vinson [MVP]

On Tue, 17 Feb 2009 15:15:01 -0800, Vincdc
wrote:

Hi John,
Yes, this is a very large questionnaire. I have gone through the
normalization process and moved redundant fields into other tables. Right
now, fields in this main table are "one-to-one relationship" and there is no
need to separate them into different tables, unless you suggest to do that to
improve performance.
I also tried to compact the database and it does not work either. I still
got the error message. However, I found that Access saved the changes I made.
It is kind of weird, no idea on it.

Seriously:

Your table design IS WRONG.

It is perfectly straightforward to design a questionnaire database with ten
questions, 200 questions, or 2000 questions...

*with no table having more than six fields*.

With your "one field per question" design, if you ever need to add, change,
delete, or reorder a question, you must restructure your table, revise all
your queries, revise all your forms, revise all your reports.

With a normalized design you add, delete, or edit one record in a table.

Do look at At Your Survey. It's a great app, and it will make your life MUCH
EASIER.

--

John W. Vinson [MVP]

I see what you mean.
Thanks!


"John W. Vinson" wrote:

On Tue, 17 Feb 2009 15:15:01 -0800, Vincdc
wrote:

Hi John,
Yes, this is a very large questionnaire. I have gone through the
normalization process and moved redundant fields into other tables. Right
now, fields in this main table are "one-to-one relationship" and there is no
need to separate them into different tables, unless you suggest to do that to
improve performance.
I also tried to compact the database and it does not work either. I still
got the error message. However, I found that Access saved the changes I made.
It is kind of weird, no idea on it.

Seriously:

Your table design IS WRONG.

It is perfectly straightforward to design a questionnaire database with ten
questions, 200 questions, or 2000 questions...

*with no table having more than six fields*.

With your "one field per question" design, if you ever need to add, change,
delete, or reorder a question, you must restructure your table, revise all
your queries, revise all your forms, revise all your reports.

With a normalized design you add, delete, or edit one record in a table.

Do look at At Your Survey. It's a great app, and it will make your life MUCH
EASIER.

--

John W. Vinson [MVP]