472,983 Members | 2,395 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,983 developers and data experts.

Risks Involved When Using Memo Fields in MS Access Databases

Memo fields in MS Access are similar to the ‘text’ fields in that they allow you to store characters. However, the size of a text field is limited to just 255 characters while a memo field allows as many as 64,000 characters. The actual contents of the memo are stored in separate data pages and the field itself stores pointer to these pages. Access allows you to limit the size of a text field via the Field Size property, but the same cannot be done to a memo field. Hence, memo fields are used when your records are larger than 255 characters and can be stored without formatting.

A prominent issue that is faced typically with memo fields is that they are sometimes cut off when processed by Access. This means when you perform aggregation, de-duplication, or formatting, Access may end up truncating the memo during this processing. The reason for this behavior is self-explanatory. When processing involves string operations, performance can be much slower than expected. A single operation may take several days as Access would require comparing tens of hundreds of characters in the memo with thousands of characters of other memo fields. In addition, comparisons may involve other things. The JET 4 MDB and ACCDB files are UNICODE-formatted. Processing these files takes more time for there are more disk reads. For these reasons, Access only processes the first 255 characters when handling memo fields.

Memo fields are more vulnerable to corruption than any other object in your database. If the pointer to the actual memo data is written incorrectly in the field, you may see garbage when you access this field. If corruption occurs to the memo field, it can affect the entire database. Even after fixing the damage, you may lose the actual data of the memo field. When multiple users in a network try to append data to the end of a memo field, the field may show up a ‘#deleted’ symbol indicating data corruption. If this field is accessed by a user, it would display the following error on the screen:

‘The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.’

If you attempt to <link removed> containing the corrupt memo field, you may receive a false report that the repair was successful. If you attempt to compact the database, you may receive another error:

Record(s) can't be read; no read permission on 'filename'.

You can remove corruption by deleting the row explicitly, and then compacting the database. You can recreate the memo field and restore the contents of the field by linking to an older backup.

A good approach to prevent memo field corruption is to either avoid using memo fields or store them in separate tables, and then create a one-to-one relationship to the first table. You can also choose to use OLE objects to store large amounts of text instead of using memo fields.
Feb 25 '13 #1
3 37502
Taaner
16
Thanks for this information Mark. I was always sceptic towards using memo-fields in a database, this article confirms to avoid the use especially in an Access database.

Should the article not been moved to "insights" since it is not a question?
Feb 27 '13 #2
zmbd
5,501 Expert Mod 4TB
The problem here that I see is that the post appears to be an aggregate of information from sources likethat has been barely re-worded and as such, without citing the sources borderlines in plagiarism.

Also, it appears that some of the issues with Memo field corruption have been fixed: memo-fields-multiuser-database (also of note is that markwillium's post even echos information found within this thread).

I'm sure that the site admins have started a review of this post.
Feb 27 '13 #3
TheSmileyCoder
2,322 Expert Mod 2GB
I have used memo fields in my databases since I started developing in Access, and I have to say I don't really agree with the contents of your post.

Yes, Access will in some cases only work on the first 255 characters of a memo field, but to be honest I don't see this as a bug, I see it as a feature, that admittedly could do with some more information/warning about how to use it. It makes perfect sense actually to restrict the query engine to not work on the entire field, especially since memo fields can in theory contain over a hundred thousand characters (although max limit on GUI is 64.000, you can add more through VBA). Know your tool.

Perhaps Memo fields are more susceptible then other types of fields to corruption. I haven't seen any empirical evidence IN MY OWN work as of yet. I am also more likely to be in a accident if I use my car rather then walk, all the same I still use my car, and I still use my memo fields. That said, you should of course not use a memo field to store text that you know will never be longer then 255 characters. Don't use a memo field for a first name. This is no different then I shouldn't use a text field to store an integer (even though possible), and I shouldn't store dates in a text field either. Know your fields, and use them APPROPRIATELY.

@Taneer
I believe the main purpose of this post was to sneak in a link (commercial spam) to a site. The link was removed by Rabbit, and I will leave the post as is. People are entitled to their opinions on Memo fields, but I don't really believe this counts as an insight.

In general if you keep your frontend seperated from your backend, and avoid doing design changes on a live database the risks of corruption are small. That said, always remember that access (unless a SQL server is used as backend) is a file based database, and that several read/writes are performed to the backend file all the time in a multi-user environment.
Make sure you have a backup strategy. Make sure that your local IT department takes regular backups, of the backend file. In 5 years of developing I have twice had the need to retrieve a backup file. One was caused by yours truly running a bad update query (wooops) and the other was caused by a complete power failure to the network drive.
Feb 27 '13 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: bjbounce2002 | last post by:
Hello, I am using forms with command buttons to close form or run action queries. The error messages such as "Null value in required field" or "duplicate value in primary key" are suppressed....
13
by: ken | last post by:
I have 5 memo fields in one of my tables. I need to add 7 more. Will this be a problem? Am I being paranoid? Thanks
3
by: Laurie | last post by:
I am using Automation in Access 2003 to open a Word Document and fill in some values using bookmarks. It all works perfectly except for one section. I am filling in some values in a previously...
1
by: alan | last post by:
I've use VB.NET+Access to write build a simple POS application. For example i got two computer, comp A and comp B. Which comp A share a Access db file through the network. When open a new bill, the...
2
by: wildfyre53207 | last post by:
Here is our problem... We are doing a lot of selects against a table that has one large field in it. If we do a select against all the fields except for description, the query comes back...
12
by: Jan | last post by:
Hi: I've got the Error 3197 problem ("The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.") in a client...
1
by: Bob Bridges | last post by:
I don't see it in the documentation, but it seems whenever I try an append command adding a value to a memo field, the query bombs. Here's a sample: DoCmd.RunSQL "INSERT INTO Who (Type, Name,...
7
by: anthony | last post by:
Is there any way that individual words or phrases can be formatted within a memo field? Obviously, the whole field can have formatting applied but I'm interested in being able to format just part...
11
by: christianlott1 | last post by:
I really think it's a shame ms access has such a buggy memo field :( A database that can't store even a paragraph of contiguous text in 2008. M$ rocks!
2
by: steph | last post by:
I have a table with 250 fields. Of course you are wondering why 250 fields... what could I possibly be storing in so many fields? I am using this table as a general import table for files that...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.