 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / June 2004Table size limits
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 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!).  SignatureMore info, please ... Jeff Boyce <Access MVP 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 [quoted text clipped - 12 lines] >space until you run a Compact & Repair (but only after creating a backup ... >or two!). Rgds Peter 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. SignatureGood luck Jeff Boyce <Access MVP 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 >-----Original Message----- >Peter [quoted text clipped - 11 lines] > > 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 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. SignatureGood luck Jeff Boyce <Access MVP |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.