 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / January 2004memo vs text
Other than longer fields and multi lines; what is the difference between memo fields and text fields? For example; Does a string of thirty characters in a memo field take up less space than the same characters in a text field defined as eighty characters? John... Visio MVP Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm Need VBA examples? http://www.mvps.org/visio/VBA.htm Common Visio Questions http://www.mvps.org/visio/common_questions.htm Text type fields are stored in the main table storage area. The actual storage depends on the data in the field, i.e. will not use up 80-characters of space if there are fewer characters in the field. A memo field is stored as a pointer to another part of the mdb file, and so counts for only a few characters towards the 2k record limit. You could easily hit that limit with a few large Text fields, but you could have well over 100 memo fields with 64k in each and not hit the limit. Because the memo fields involve a pointer, there is a slightly increased chance of corruption on an unstable network/computer. Although text boxes default to multi-line for memo fields, you can set the same properties for a text box bound to a Text field. If actual disk space is a factor, the Unicode Compression property will also be a factor.  SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Other than longer fields and multi lines; what is the difference between > memo fields and text fields? [quoted text clipped - 6 lines] > Need VBA examples? http://www.mvps.org/visio/VBA.htm > Common Visio Questions http://www.mvps.org/visio/common_questions.htm So I need not be stingy with defining the size of the text fields. Thanks. John... Visio MVP PS: You may want to remind Tony Toews that you are not at http://users.bigpond.net.au/abrowne1 Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm Need VBA examples? http://www.mvps.org/visio/VBA.htm Common Visio Questions http://www.mvps.org/visio/common_questions.htm > So I need not be stingy with defining the size of the text fields. There is a subtle problem to watch out for, though. You can define a record with, say, ten fields of Text(255), and happily type stuff into them. There is, however, a limit on the size of any one record at 1 KB, and one day someone will put 101 chars into all ten fields and will get an inscrutable error message about record sizes. I am not sure if this error is trappable. If there is _any_ chance of getting up to 1K per record, then you need to rethink the design, either to use more than one table or to use memo fields. As Allen says, the content of the memo does not count toward the 1KB, only the eight bytes or so of the pointer. Hope that makes sense Tim F Makes sense, but I'm approaching the limit from the other side. The original data is on 5K records that use an overlay to interpret the record. So to modify one field the entire record is read and then updated. Also using overlays means that for some overlays there is a significant waste of space. The new version has these 5K blocks broken down into more logical groups with the largest approaching 500 characters. John... Visio MVP Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm Need VBA examples? http://www.mvps.org/visio/VBA.htm Common Visio Questions http://www.mvps.org/visio/common_questions.htm > > So I need not be stingy with defining the size of the text fields. > > [quoted text clipped - 12 lines] > > Tim F (snip) > As Allen says, the content of the memo does not count > toward the 1KB, only the eight bytes or so of the pointer. 10, methinks :-) (We all needed to know that!) TC >(snip) > [quoted text clipped - 6 lines] > >TC <pedantic mode>14 in A97 and before, 16 thereafter</pedantic mode> Now dammit I can't find the reference... but I'm pretty certain that's correct. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public >> As Allen says, the content of the memo does not count >> toward the 1KB, only the eight bytes or so of the pointer. > > 10, methinks :-) Oops: yes of course. Comes of writing from an ageing memory rather than checking help files. Still, the logic remains pertinent. Thanks and best wishes Tim F |
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
|
©2008 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.