Table size limits

[Peter C]

I have built a table which has ~ 150 fields, and when I
try to add more I get the rather cryptic error 'Property
value too large' and the help button gives a blank help
page.
I am below the 255 field limit - so that is not the
problem.
At an earlier point I got the slightly more helpful error
message that I had 'exceeded a maximum of 2000', which I
suppose might be the number of characters per record
limit of 2000, but it doesn't say anywhere that I can
find what this includes - is it just field size, is the
field caption included ?
I have tried reducing all of these in previous fields,
and then adding the additional fields - but to no avail -
I'm stumpted
HELP

 

[Jeff Boyce]

Peter

Having 150 columns is not unusual ... for a spreadsheet! But a scan through
this (tablesdbdesign) newsgroup will reveal a common discussion on this
point. Access is a relational database, and you only get the full value and
power of the tool if you design your table structure relationally.

If you provide more information about what kinds of information you are
storing in those fields (and by scanning you'll see that even 50 fields is
perhaps too many!), the 'group readers may be able to offer alternatives.

The messages that you are getting may be related to the fact that Access
keeps track of all the attempts to create fields, and doesn't reclaim the
space until you run a Compact & Repair (but only after creating a backup ...
or two!).

 

[Peter C]

Thanks Jeff for the feedback
The database is to analyse a survey in our local
community. Each respondent has ~170 questions (usually
tick boxes)to answer so it seemed to make sense to have
just one table.
I could sectionalise it and have many different tables
but this gets messy
I have edited the field types, avoiding long text fields
wherever possible to avoid the specfied 2000 characters
per record limit - although I don't really know what is
included in this limit and what is not, and neither does
the literature I have define this
I have tried a compact and repair many times.
And still no success - I don' know and can't find out
what is limiting the size.
HELP again

-----Original Message-----
Peter

Having 150 columns is not unusual ... for a

spreadsheet! But a scan through

 

[Jeff Boyce]

Peter

If you took a spreadsheet and split the 170 columns in groups of 30 to
individual sheets, you'd still have a spreadsheet.

Splitting the Access table columns containing question responses (Q1, Q2,
.... Qn) into multiple other tables still doesn't address the primary issue.
Repeating fields (Q1, Q2, ...) are not a well-normalized design in Access,
and will not allow you (or Access) any easy way to do what you're trying to.

Duane Hookum has crafted a survey design creator in Access -- take a look at
what he's done for more ideas on how you might re-structure your data:

http://www.rogersaccesslibrary.com/duanehookom/duanehookom.htm

If you feel you must keep your current structure (170 Q's as columns),
consider using Excel.

 

[Peter C]

Thanks for all the suggestions - I'm not sure I
understood what you were driving at in the last answer,
but I did look at Duanne Hookum database but couldn't see
how that helps.
I don't want to use Excel as the people who will have to
enter the data would find a 170 column spreadhseet very
unfriendly.
And I'm still stuck with this irritation that Access just
dosen't do what it's own specification says it should and
it doesn't seem possible to find out why not - without
paying MS to help that is

Rgds

Peter

 

[Jeff Boyce]

Peter

If the spreadsheet format is the only irritant, and you would otherwise be
using Excel, take a look at the Data|Form command. This gives you a way to
build a (very rudimentary) form for data entry, but puts the data into the
spreadsheet.

About your irritation -- which specific specification are you referring to?
If it is the 255 field limit, see my earlier comment about using Compact &
Repair to reclaim internal space.

The limit of 2000 characters per row is another reason not to use a
spreadsheetly design -- it's too easy to add too many fields and not be able
to put values in them all.

Do give "normalization" a look. Reconsider Duane's database - it will help
you design a well-normalized survey database.