Why One Should Avoid Using Memo Fields for Grouping in Access

In this article we would learn the key aspects related to memo fields and learn why they are not really helpful and often result in bugs and truncation.

A memo field can store large amount of information, allowing up to 65,536 characters, with various options. In the latest editions of MS Access, it can store up to 1 GB of characters and allows rich text formatting. But it is often suggested to avoid the use of Memo fields for purpose like grouping in MS Access. The article will discuss the reasons why Users should avoid Memo fields and why they are ineffective.Why You Should Avoid Using Memo Fields For Grouping

Memo Fields

Memo Fields In AccessIn the 2009 version of Access and in later ones, there was a bug in which the memo field would display wrong and inaccurate characters under certain conditions. If the user uses ‘GROUP BY’ query on a memo field or if the query contains a JOIN on an unindexed field, the memo field would show inaccuracy. Access would just show random characters in place of the contents of the given memo field.

MS Access basically truncates the memo if the user wishes to process the data depending on the memo. The most common causes that lead the truncation are Aggregation, Uniqueness, formatting, union queries, etc.  For example A Union query, which is used to combine the values from different tables and then de-duplicates them. It then compares the memo field and results in its truncation.

Why Memo Fields Are Ineffective

The basic reason why MS Access doesn’t allow more than 255 characters is because it would tremendously slow down the performance, as string operations are disk and processor intensive. Some data sources process strings as bytes and some use Unicodes and thus is becomes incomparable. It however saves the data to the table but it’s inefficient in working with the extra data.

The characters might not be same for different settings and Jet (Microsoft Database engine) is strictly case-sensitive.

Let’s take an example. A user has two tables, One and Two, former having 2 fields and latter having 1. Table one is with Field A which is a number having long integer with 2 records (Say 1 and 2) and another Field B having texts, one and two.

Table Two is having one Number field which is another long integer and has two records as 1 and 2, and both the tables are without index.

The query will join the two tables and the groups with the fields in TableOne, but it will eventually show an incorrect output of Field B. That is why the use of Memo fields for grouping purpose should be avoided.

Alternatives

Users can avoid grouping by the Memo fields and instead choose first in the total row provided under the memo-field. This is an effective technique as it won’t show bugs/errors and would also return the memo field without truncating at it 255 characters. Users can also index those fields which are involved in a join.  Users should also avoid using memo fields in combos or in list boxes.

MS Access despite being a very popular database application is far from being perfect. Even the data stored in Access database can come at risk during a database crash. To avoid such a scenario, Access users should keep an mdb repair tool handy.

Author Introduction:

Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including corrupt SQL Server and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.