using memo fields: wise or to avoid

[pierre]

Hi all,

In a 5000 records table i need a string field that will be less than 255
char. Most of the time a record is around 130 char

1. Is it better to use a memo field?
2. will a memo field use less space?
3. are there issues to memo field? should they be avoided?

best regards

pierre

 

[mscertified]

Memo fields will be slower to process.
Generally speaking datatypes that hold less data are faster to process.
However, I don't think it makes any difference whether you use String(130)
or String(255)
Your table is very small to be worrying about this. If you had hundreds of
thousands of records, I'd be more concerned.

-Dorian

 

[mscertified]

also see http://office.microsoft.com/en-us/assistance/HP051875211033.aspx

when sorting on a memo fields, only the first 255 characters are used.

-Dorian

 

[John Vinson]

Hi all,

In a 5000 records table i need a string field that will be less than 255
char. Most of the time a record is around 130 char

1. Is it better to use a memo field?
No.

2. will a memo field use less space?
No.

3. are there issues to memo field? should they be avoided?

They should be used when they are needed - i.e. when you must handle
text exceeding 255 bytes. A Memo is stored separately from the table,
in a different part of the database; storing 136 bytes in a Text field
takes up 136 bytes plus two bytes of system overhead (if I recall
aright). Storing it in a memo field will take the 136 bytes plus 16
bytes of overhead.

Memo fields have some disadvantages: they cannot be indexed; if you
sort, format, or group by the field, it will be truncated to 255 bytes
(for that query, it won't damage the stored data); they are slower to
display and search than text; and they are more prone to corruption of
the database. However, sometimes you DO need the longer text - if so,
use a memo, accept the disadvantages, and keep your backups current!

John W. Vinson[MVP]

 

[Jerry Whittle]

I'll add that Memo fields sometimes cause corruption problems. As John
stated, the Memo field data isn't really part of the record; it's stored
elsewhere. When the link from the data to the table gets broken or confused,
you have a corruption problem. It often manifests itself when you tab into a
memo field and Access just shuts down!

If you can, avoid memo fields.

 

[david epsom dot com dot au]

Also last I looked, memo's still had the grouping bug:

http://groups.google.com.au/group/microsoft.public.access.queries/browse_frm/thread/f88b17aa13908980

(david)

 

[Stephen Lebans]

A lot of issues can cause corruption but using a Memo field in itself does
not. Is the Memo field more prone to corruption than a standard field...well
yes because of the nature of the field's storage. But still, in 7 years of
using Access I have seen lots of corruption in forms, reports and VBA
modules but never in a Memo field, which I use extensively. You're more
likely to suffer corruption of a form or report object than a Memo field.

Use Memo fields...sure!
Institute proper backup procedures...yes! You have no one to blame but
yourself if you don't.

Just my $02 worth every cent you paid for it
--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[TC]

Jerry, the data in a single record can be split up all over the place,
within an mdb file. This is not limited to memo fields. The data for
the numeric & non-memo text fields can be split up into one or more
continuation data pages. And there are loads of other internal links
within an mdb file. A complex file structure like a Jet mdb file, is
fundamentally dependent on the correct operation of hundreds or
thousands of internal links - including but not limited to those for
memo fields.

HTH,
TC [MVP Access]

 

[Allen Browne]

Yes, that bug is still present in Access 2003 SP2 with JET 4 SP8.
Thanks for the pointer.

Pierre, you already have lots of good responses. My suggestion would be
always use Text if you are certain that the field will contain <255
characters, unless you have many wide fields and are concerned that the
whole record could become >2000 characters wide. Memos will help work around
that limitation.

 

[david epsom dot com dot au]

in 7 years of using Access I have seen lots of corruption ... but never

in a Memo field,

:~)

I had to smile, because that is correct :~)

There was a specific problem with Jet 3.51 sr2
(and possibly earlier versions?). Anyone who
doesn't have white hair won't have seen memo
corruption - it's just part of the collective
mythology.

(david)

 

[david epsom dot com dot au]

Corruption of out-of-line indexes was a problem in
dbase 4, and before that in Cobol, and before that
in paper based file systems. It's worth celebrating
that the current absence of memo index corruption
represents a triumph of 50 years of computer
development. :~)

(david)

 

[TC]

Indeed, well put.

My first experience of program corruption, occurred in a program
comprising 400, 80-column Hollerith punchcards.

I dropped the friggin' deck on the floor, & it got all mixed up! :-((

TC [MVP Access]

 

[John Vinson]

Indeed, well put.

My first experience of program corruption, occurred in a program
comprising 400, 80-column Hollerith punchcards.

I dropped the friggin' deck on the floor, & it got all mixed up! :-((

LOL!!!

I learned early on how to get the cardpunch to automatically put a
sequence number in 73-80.

Then dropped an 80-column *binary* compiler output deck... :-{(

John W. Vinson[MVP]

 

[TC]

Gak!

Do you remember the IBM Card Verifiers?

They looked just like a normal card punch. But you had to put an
already-puched deck in the hopper, and retype what you typed before.
The verifier would check each card, to see if it matched what you
typed. If it didn't, the verifier would emit a soft "blurp", and give
you two more tries. Then it would flash a big red light, and punch an
enormous hole in the middle! By that means, you could verify a deck by
retyping it, then take out the few bad cards to do them again.

Beginners would queue up for a verifier, thinking it was a normal
punch. The old hands would watch them sit down, load their deck of
*blank* cards, and start typing their program! Hilarity then ensued:

type type type ... Blurp
?
type type type ... Burp
??
type type type ... WHACK! (chomp)
????? !!!!
(press feed button for next blank card)
type type type ... Blurp

and so on !!!

TC [MVP Access]