Is it bad to use Memo Data Type? (Access 2000 – 2007)

Topic

New Reply

I do not know the dynamics of a memo field, but if it works like a text field, I will hesitate to use it. A text field stores up to 255 characters. I am aware that it is good practice to determine the field size and limit the character storage to just more than what is necessary. My question is: Does a memo field work like a text field? What happens if I need only 300 characters in a product description. Is it going to store 65200 extra characters per record that I do not use? I cannot see that this will be the case. So how does memo work, how does it store data or determine how much to store? I will appreciate a bit of an explanation on this as I am always hesitant to use memo!!

TX

Reply | Quote

Replies

The essential difference between a text field and a memo field is that a text field is stored within the table, while a memo field is stored separately within the database, but outside the table. For a memo field, Access stores a pointer in each record to the place where the text is stored. This makes memo fields more vulnerable than text fields – Access has to keep those pointers up-to-date, and sometimes they get corrupted. Some people maintain that memo fields invariably lead to database corruption, but that appears exaggerated to me. Perhaps I’ve been lucky, but some of my database containing memo fields have been in use for 8 years now, used by many people every day, without serious problems.

For both types of fields, Access stores only what is needed. Even if the field size of a text field is 200, the text “Rudi” will take up only 5 bytes (a byte indicating the length of the string, plus the 4 characters; if you use Unicode characters, the actual size will be a bit more). A memo field takes up a fixed number of bytes in the table itself (for the header), plus as many bytes in the separate storage as are needed for the text.

Reply | Quote

**while a memo field is stored separately within the database, but outside the table.** – Huh!!!
You have just thrown me a curve ball here. I thought tables were the storage containers of a database. If data is not stored in a table, where is it put? Sorry if I sound dumb here, but this is news for me. Its like you are trying to explain to me a secret from another dimesion??

PS: thanks for the info and the informative way you attempted to explain something that is very foreign for me. I really appreciate it Hans. Wow, I wonder if I should dare to query this further as I fear your answers will make me doubt my compitence in the application?? (I thought I knew Access a little better than the average person, now I feel like a novice again). Its a weird feeling!

Reply | Quote

If you look up the Specifications help subject, you’ll see that a record in a table has a maximum size (2,000 bytes in Access 2000/2002 and 4,000 bytes in Access 2003/2007), and that this excludes Memo and OLE fields. Since Memo and OLE fields can hold very large amounts of data, they don’t fit inside the record itself.

Therefore, Access stores the contents of Memo and OLE fields in a special storage space in the database (invisible to the end user). The fields in the table itself contain only a pointer to where the contents are stored; this pointer takes up just a few bytes.

See picture below (text from Wikipedia)

Reply | Quote

TX Hans, you certainly do not duck out of explaining the concept.
I appreaciate the lesson.

Reply | Quote

To add a bit of minutia to the discussion, Access stores data in chunks called pages – in older version it was used for record locking purposes as well as for places to store data. In 2002 and 2002 those pages are 2048, which accounts for the limit of approximately 2000 bytes in a record. In 2003 and 2007 that size increased to 4096 or approximately 4000 bytes in a record. I’m not certain of the storage scheme for memo fields, but it is entirely possible that only one memo field record can be stored in a page. The implication of course would be that having a memo field with data in it uses one page for each record. Does someone have detailed knowledge about that?

Reply | Quote

As noted higher up in this thread, memo fields (and OLE fields) are not stored in the table itself, but in a separate BLOB (Binary Large OBject) storage within the database. The records in the table contain a pointer to a location in the BLOB storage; this pointer takes up only a small number of bytes (8 or 16, I forget which).

Reply | Quote

What I was trying to suggest, but didn’t do very well, is that putting data into a memo field (BLOB) may well take up an entire page for the BLOB, even if it’s only a few bytes. I suspect that the pointer in the actual record points to a page, and not to some sort of offset within the page – but that is strictly conjecture on my part.

Reply | Quote

Thanks for the clarification.

I have no reference for it, but I think that blob storage is organized differently.

To test, I created a blank database, then created a table with an AutoNumber ID field (primary key), a text field and a memo field, then compacted the database.
I entered a character in the text field (thereby creating a first record), closed the table and compacted the database.
Database size increased by 4 KB.
I entered some characters in the memo field in the first record, closed the table and compacted the database.
Database size remained the same.
I created a second record, entered some characters in the text field and memo field, closed the table and compacted the database.
Database size remained the same.

This is not conclusive, of course, but it appears to show that Access doesn’t create a new page for each non-blank memo field

Reply | Quote

A lot of information has been posted already. Text fields are limited to 255 characters, because the first byte of storage represents how many characters will follow, so if there are 10 characters stored for the text in a field, in the actual .mdb file, where that field ‘starts’ will be one byte with the binary value of 10, followed by the 10 bytes representing the characters of the field. So Access never takes up more space then necessary for text fields, due to this prefix byte.

Memo fields, as already explained, are stored outside of the table structure (from the standpoint of the actual file structure of an .mdb). Size wise, however, Memo fields are in theory allowed to take up the entire space of the database. The 65k character limit is only applicable when the data is entered through the Access interface, such as a form, or table view. If you put data into a memo field programatically, you’re limited to the physical disk space limit of the .mdb (1 gig in 97, 2 gig in 2000 and later).

However, the main reason I am posting to this, is that there are other differences which should be taken into consideration when designing a database structure. First, Text fields can be indexed, Memo fields cannot (at least Access won’t index them…). So when data is entered into an indexed text field, doing a search in that field will have jet hit the index. Doing a search in a memo field will have Jet running through the entirety of every applicable memo field. If you have hundreds of megs of text, that’s a lot to run through.

Also Memo fields have some issues with queries. Off the top of my head, I know you can’t group by a memo field, you can with a text field.

Don’t misunderstand me, I use memo fields all the time. I use them when I have no idea how much text is going to be entered. If I know there’s a limit, I use a text field. and if the limit is over 255, but can be dealt with by multiple text fields, sometimes I’ll design the table to have multiple text fields, to include the indexing/querying capabilities of a text field.

Reply | Quote