Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Melbourne, Australia

    Unanswered: Memo versus Text

    Hello again Colleagues All,

    In another thread, the matter of using memo and text data types came up but was dismissed with the remark, "you only need memos when there are more than 255 characters". The context of my inquiry is that i have a database with many situations where a note is required. I am concerned about the amount of space these use up, even when blank. Perhaps it would help if I explained where I am coming from.
    In a previous existence I worked with Informix, which I still think is one of the best products around (even if it has been taken over by IBM!). Informix had char and varchar data types, like other products, but the varchar spec allowed a minimum number of characters to be specified. What actually happened under the covers was that the field of X characters was filled up first and the balance went into an area defined by a pointer. This made sorting and testing quite easy, but more importantly it gave you very good control over space, when not in use.
    I am wondering how Access text and memo types work under the covers. If I have a lot of empty note fields (which will probably be the case most of the time), megabytes of disk space could effectively be taken out of service. I presume that memo fields work essentially like varchars in other systems; that is, there is a pointer to the storage area. It would appear then, that, contrary to the earlier advice noted, using memos, even for quite short notes, might be better, if a lot of fields are going to be blank.
    This brings us to the other issue, which is performance. Is a memo area nominated whent the record is created, or only when it is populated. If the latter, any test for content using IIf or Nz or whatever, will immediately be satisfied by the fact that the pointer is null. On the other hand, if the program has to read the pointer and then go to the storage area, only to find that there is nothing there, then there will be a lot of disk movement to little benefit.
    Could someone enlighten me, please.

  2. #2
    Join Date
    Jan 2007
    Provided Answers: 10
    You could disconnect your memo fields from it's current table and put it in a separate one, only creating entries where necessary. Then all that's needed is a simple join between your memo table and current table
    Home | Blog

  3. #3
    Join Date
    Dec 2002
    Préverenges, Switzerland
    don't tie yourself in knots with this stuff: JET is quite clever.

    example MDB that i use to suck data from one system and pump it into another system.
    this afternoon, the main table holds 10,955 records.
    the table looks like this:
    2 x Long (8 bytes)
    10 x 255 Text (2550 bytes)
    1 x 25 Text (25 bytes)
    1 x 50 Text (50 bytes)
    2 x DateTime (16 bytes)

    ignoring all the complications, this totals 2649 bytes/row if you simply add the field sizes together.
    so does it take 29MB to store this table?
    NO WAY!
    in reality, the whole .MDB weighs in at 4MB total or 4,177,920 bytes to be precise (MDB includes a couple of smaller lookup tables, one form with the pull-it and push-it code, plus the normal MDB "overhead")

    by habit i avoid memo fields whenever possible, i often prefer to split longer text into several Text(255) fields rather than including a memo field. i'm not saying memos are bad, i'm not blaming memos for causing occasional crashes in one of my me-only-user utilities, but i do know that recovering that utility from a crash shows the memo field that i most-recently edited is empty (...which it wasn't when i finished editing it).

    currently using SS 2008R2

  4. #4
    Join Date
    Oct 2004
    Melbourne, Australia
    Excellent, guys! Just the sort of stuff I needed to hear. I particularly liked your suggestion for storing comments in some sort of sub-table, George. If I do this I do not even need to use memos as 255 bytes is adequate. The usefulness of the idea is that if there is no comment, there is no record, which is a spacesaver. Of course, one must remember to do an OUTER JOIN when recovering the data. From my perspecive, there is an additional advantage. Most of the notes relate to call centre staff explaining why they have departed from standard and this situation can occur at different points in the process. I can actually store all of the comments in one large table with a key to identify the source (i.e. a form). This will allow the CRM people to analyse needs and so forth, and also identify where staff are not using the system properly (though I do feel badly about exposing them to 'counselling!).
    Thanks to you, too, Izy, for your knowledgeable comments. It just shows how one can be trapped by ones extensive exposure to one environment into thinking that its features must be ubiquitous.
    Jim W.

  5. #5
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    I've always worked with health care systems, and hence have always used memo fields for such things as patients'/nurses' notes, and so forth. I've never had any problems with them because I stick to one absolute rule: Never, never put anything in memo fields that will ever need to be sorted, parsed, searched or in anyway manipulated! Place notes (aka memos) in it never expecting to do anything more complex to it than printing it.

    Trying to do many kinds of manipulation results in truncation of the memo field to 255 characters. Allen Browne has an excellent paper explaining problems that cause this truncation, most of it relating to queries:
    Hope this helps!

    The problem with making anything 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