Jürgen Welz
jwelz at hotmail.com
Fri May 21 15:27:24 CDT 2004
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=http://hotmail.com/enca&HL=Market_MSNIS_Taglines