Memo vs. Text 255

[Anthony Bollinger]

I have taken over a database with a Memo field in it. About one-fourth of
the records have data in the Memo field. All of the field lengths are less
than 255, with 75% of them being under 100 characters. What are the data
storage specs for Text and Memo? Is text just a fixed-size block? I am
wondering if it makes sense to stay with memo, or if going with text 255
will have any advantages?

Many thanks,
Tony

 

[Jeff Boyce]

Anthony

If I remember correctly, Access doesn't use a fixed length block for text,
but stores as many characters as entered, up to whatever limit is set. It
may be, however, that setting a small limit (e.g., 2 text characters to hold
State abbreviation) reduces the overall size, I'm not sure.

If your only concern is storage specs, consider the potential extra work
Jamie points out from converting the data type. Have you checked Access
HELP on "specifications" to see storage specs?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Jerry Whittle]

A text field of 255 does not use up 255 characters for each record. Access
doesn't waste space like that.

Memo fields have certain limitations such as sort and grouping plus other
problems. One of these problems is corruption issues. Memo fields are much,
much more likely to corrupt than a text field and when they do, compacting
and repairing is less likely to fix the problem.

IF (big IF) the users will never, ever need to put more than 255 characters
into this field, I would convert it to a text datatype.

Oh! Check the lenght of all the other fields, including number fields, in
the table first. Access has a limitation of 2,000 characters per record,
excluding memo and OLE fields. It's possible that the designer thought that
the 100 or so characters in this field may put the record over that
limitation and decided using a Memo field was a good compromise.

 

[David W. Fenton]

I have taken over a database with a Memo field in it. About
one-fourth of the records have data in the Memo field. All of the
field lengths are less than 255, with 75% of them being under 100
characters. What are the data storage specs for Text and Memo?
Is text just a fixed-size block? I am wondering if it makes sense
to stay with memo, or if going with text 255 will have any
advantages?

Others have explained various aspects of the question. One that
hasn't been mentioned is that memo fields are *not* stored with the
main record. All that is stored in the record is a pointer to the
data page where the memo data is stored. This means that memos are
somewhat more fragile than other data types, because the pointer can
get broken fairly easily.

I would only use memo fields where they are definitely required.

 

[Jerry Whittle]

True. I can do the same thing in Oracle using CHAR; however most Oracle
developers now use the VARCHAR2 datatype. In a similar vein, most Access
people use the default tools for table design to create fields with the text
datatype.