Charlotte Foust
cfoust at infostatsystems.com
Mon May 24 13:02:17 CDT 2004
Single table query on a table with all 255-char text fields returned a "query too complex" error. Charlotte Foust -----Original Message----- From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com] Sent: Monday, May 24, 2004 9:10 AM To: accessd at databaseadvisors.com Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various Actually, I would say that relying on Jet to do data validation is lazy programming, not the other way around. Lets say you were storing State abbreviations. So you limit your field size to 2. Why limit the field size, when you can just force data entry through a combo box (or even use a lookup field...LOL, sorry, couldn't resist). When the US takes over the entire world, we may have to go to 3 letter abbreviations. When that happens <evilgrin>, if you have limited your field, you now have 2 changes to make, instead of 1. Once again, I've been burned quite a few times by other people's field size limitations, and I have NEVER been burned by fields being set to 255. Just out of curiousity, how did the 255 character fields bite you in a query? Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte Foust Sent: Monday, May 24, 2004 11:51 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various Not a valid argument on the phone numbers, Drew. International phone numbers are still not going to be more than about 15 characters, so why make the field 255? I've been bitten by 255 character fields in queries, so I tend to "limit" the field size to something reasonable for its contents. No one could ever remember a 255 digit phone number, so it isn't likely that a full size field would ever be needed for that. It's just lazy programming to default to 255. <VBG> Charlotte Foust -----Original Message----- From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com] Sent: Friday, May 21, 2004 1:54 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various I think we are talking apples and oranges here. Yes, the page file size needs to be taken into consideration. I set all of my text fields to 255. I do this because I don't want to be backed into a wall, because I set a size limit that prevents a user from entering what they need to enter. If I provide a phone number field, and set it to 10 (area code and phone number), sure, I am 'limiting' the client. However, what happens when they want to put in an international number. Or if the US decides to move to 8 digit phone numbers. Who knows, there are all sorts of reasons that the field size may change. Now, if I have some sort of logic checking data integrity, that would have to be changed, but if I don't, by having the field size set to 10, I am limiting the users at the table level, to a point where they cannot do their job. If I have it set to 255, I am 99.99999% they would never put 255 characters into that field, but they may put in 11, or 12, etc. Oh well, this really isnt' something I feel like arguing about. I see your point Jürgen, but this is really a case of who has been burned and how. I have been burned over and over by previous developers putting such limitations into their databases. I have never been burned by the page file size. In fact, I completely forgot that the limit even existed, until it popped up on the list a few weeks ago. So that is why I set my default text field size to 255. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jürgen Welz Sent: Friday, May 21, 2004 3:27 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various If you assume you have an autonumber field, a couple date fields, a few foreign key fields together with 10 text fields and you place no restriction on the text field size, you will find that there are verbose users who are unable to save records because the non text fields use up some 50 to 100 bytes leaving space for only about 1950 characters in the record for ten text fields. Allowing 255 characters in ten text fields is fundamentally flawed with such a table. If you have 20 text fields, allowing 100 characters per field will result in record save failure. Statistically, there will be few situations where an entry person would enter the maximum amount of data in all 10 or20 text fields but the design does not prevent a record save failure unless some restriction is placed on a few of the fields. You could trap the error and offer to truncate fields or dump copies into a memo field but it is safer for people learning at a community college level to restrict text field length so that the total of all fields cannot exceed record size limits. In cases where a user needs to enter more text than some realistic limit, I find it useful to provide a notes memo field. I have also used a system where overflow data was placed in a separate table on a field by field basis and reconsituted in the display field. Back in the old days of A97 before SR1, memo fields were particularly buggy and I built a system that stored text in a table consisting of fields identifying the source table, the autonumber record ID the field and the overflow data. A similar system can be used to all text fields with 300 characters while preserving the ability to Union tables (can't with memo) and to sort on (the first part anyway) of memo fields (not that it makes sense to sort on them but...) If you invite users to enter 255 characters in a phone number field or a postal code field, I can guarantee that they will. And when you sort or filter on that kind of data, the results aren't promising. Ciao Jürgen Welz Edmonton, Alberta jwelz at hotmail.com >From: DWUTKA at marlow.com > >Oh no, haven't forgotten, but if the limit is 255, that doesn't >actually affect the 2k character limit. Actually putting 255 >characters in will, but not the actual field size definition. However, >if you limit a field to 35 characters, because you think that's all a >user will need, and one day they need to put in 36 characters....NOW >you are preventing the record from being >saved. I have found very few instances where there was an ironclad reason >to set the maximum field size to something other then 255. > >Drew > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jürgen Welz >Sent: Friday, May 21, 2004 1:02 PM >To: accessd at databaseadvisors.com >Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various > > >"Unnecessary" size restrictions on fields ensure that a record can be >saved. > > If you have more than 8 text fields at 255 characters per field, >Access will refuse to save the record. Have you forgotten the the 2k >character limit on record size in mdb files? > >Ciao >Jürgen Welz >Edmonton, Alberta >jwelz at hotmail.com > > > > > > >From: DWUTKA at marlow.com > > > >Actually I think that talking about Access training/experience is a > >valid topic. > > > >Recently, I have helped a friend with his Access 'homework' from his > >MIS course. I worked cheap (for beer <grin>), but it just kills me > >what they are having students do. The 'instructions' for the > >assignments has them naming tables with spaces, setting unnecessary > >size limitations for text fields, etc. Just plain ugly. In fact one > >instruction was so goofy, I completely bypassed what they were trying > >to do, and wrote some VBA to >have > >the same result. > > > >It is just flat out amazing that the world moves as it does. <grin> > > > >Drew _________________________________________________________________ MSN Premium helps eliminate e-mail viruses. Get 2 months FREE* http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=htt p://hotmail.com/enca&HL=Market_MSNIS_Taglines -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com