If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table 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 |
#2
|
|||
|
|||
Table size limits
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!). -- More info, please ... Jeff Boyce Access MVP |
#3
|
|||
|
|||
Table size limits
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 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!). Rgds Peter -- More info, please ... Jeff Boyce Access MVP . |
#4
|
|||
|
|||
Table size limits
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/d...uanehookom.htm If you feel you must keep your current structure (170 Q's as columns), consider using Excel. -- Good luck Jeff Boyce Access MVP |
#5
|
|||
|
|||
Table size limits
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 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/d...om/duanehookom ..htm If you feel you must keep your current structure (170 Q's as columns), consider using Excel. -- Good luck Jeff Boyce Access MVP . |
#6
|
|||
|
|||
Table size limits
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. -- Good luck Jeff Boyce Access MVP |
Thread Tools | |
Display Modes | |
|
|