memo vs text

[John Marshall, MVP]

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

 

[Allen Browne]

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.

 

[John Marshall, MVP]

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

 

[Tim Ferguson]

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

 

[John Marshall, MVP]

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

 

[TC]

(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

 

[John Vinson]

(snip)



10, methinks

(We all needed to know that!)

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.

 

[Tim Ferguson]

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