Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Memo fields... Should I Avoid at all costs ????

Status
Not open for further replies.

jeep2001

Programmer
Dec 15, 2005
134
US
I stumbled onto an Access site where I read something about do's and don't do's....One of the issues was to avoid at all costs -- memo fields that they can bring down a database? I never heard that one, but what kind of havoc can they reek? I am about to do an update to one of my databases and one of the changes was to make 2 fields MEMO from text....

Should I avoid the memo fields if I can????
 
Memo fields cannot be indexed and they cannot participate in relations. They cannot be used in SQL JOIN ON conditions. There are also significant possible problems when you attempt to INSERT or UPDATE memo fields (usually truncation if you don't do it right). The presence of a memo field in a table (or worse, several of them) can drag your performance down.

The essence of the problem is that memo fields are not "in" the table. They are stored elsewhere and the table contains only a pointer to them. Because of that, most SQL that works on other fields doesn't work on Memos because SQL doesn't understand pointers.

"Avoid at ALL costs ???"

That's a business call but be prepared to deal with issues like the above.
 
I have created lots of applications with 1,000s of tables. I haven't had issues with memo fields. You need to be careful how you use them. Normalization really helps as well as good network connections.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
I don't think you need to "avoid them at all costs". Sometimes they are the right solution, just don't use them if a text field up to 255 characters will do.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top