Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    141

    Answered: Memo Fields and Table Corruption

    Greetings again,

    I have (as a volunteer) set up a database for a food bank, and the users would like to be able to retain longer ( ie. greater than 255 characters) comments pertaining to clients. I know about memo fields (as a solution), but have read negative criticism of their use, especially pertaining to data base corruption.

    The database as it now exists is reasonably small (less than 10.000 records), is front-end back-end configured (the back end is on a small server) and can have up to six simultaneous users (though there are rarely more than 2 users at any one time). If my users are being good, they back up the back end daily to their individual computers (automatic if they push my "backup" button).. One option, I guess, is to have linked tables to an external database (housing the memo fields). All very messy.

    Anyone have any experience using memo fields? Any advice.?

    J. Smith
    Aylmer, Quebec

  2. Best Answer
    Posted by Missinglinq

    "One of the Cardinal rules to having a well designed, normalized database is that no single field will contain more than one individual piece of data. Data stored in Memo fields should be restricted to "notes" or narrative type information, such as a salesman's notes on a customers, a doctor's progress notes on patients, or, as in your case, client notes.

    Working primarily in a Heath Care environment, I've used Memo fields extensively for a number of years, without ever having problems, corruption or otherwise. I've always attributed this to the fact that I've followed one simple rule:

    Never, never, never place data in a Memo field if there any possibility that you will ever need to search, sort, parse or in any other way manipulate the data!

    Because many Queries perform some of these functions, you have to take care in using them in Queries or they will likely be truncated to 255 characters.

    Allen Browne has some excellent advice explaining how to deal with Memo Fields and Queries:

    Microsoft Access tips: Truncation of Memo fields

    Linq ;0)>"


  3. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    One of the Cardinal rules to having a well designed, normalized database is that no single field will contain more than one individual piece of data. Data stored in Memo fields should be restricted to "notes" or narrative type information, such as a salesman's notes on a customers, a doctor's progress notes on patients, or, as in your case, client notes.

    Working primarily in a Heath Care environment, I've used Memo fields extensively for a number of years, without ever having problems, corruption or otherwise. I've always attributed this to the fact that I've followed one simple rule:

    Never, never, never place data in a Memo field if there any possibility that you will ever need to search, sort, parse or in any other way manipulate the data!

    Because many Queries perform some of these functions, you have to take care in using them in Queries or they will likely be truncated to 255 characters.

    Allen Browne has some excellent advice explaining how to deal with Memo Fields and Queries:

    Microsoft Access tips: Truncation of Memo fields

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #3
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    141
    Thank you for allaying my fears -- the logical route is memo fields. And I will make it clear that we can never attempt to parse, manipulate or query the memo fields. I think I will also push them to use only a single memo field ( or they will want a memo field for everything). There is always this strange belief by users that if they enter it in the computer, it can be counted.

    Thanks again,

    J. Smith
    Aylmer, Quebec

  5. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Just like yourself, I've seen posts that blame Memo Fields for corruption that has been experienced...but on digging deeper, have always found that they were trying to do the things that my post advises against.

    If it's something you might need to search, sort, parse or manipulate, in any way, it needs to be in its own, discrete Field! You can also mention it in the Memo Field, i.e. a doctor mentioning a patient's elevated temperature, but have it elsewhere for manipulation.

    Good luck with your project!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •