Flaws in Microsoft Access

Identified by Henrik Bechmann.  Provided by Allen Browne, December 2006. Updated July 2008.

Concatenated fields yield garbage in recordset

If you combine text fields, any characters after the first 255 are garbled when you open a Recordset.

The bug affects:

• all recent versions of Access (including Access 97, so probably earlier ones too.)
• both DAO and ADO recordsets.
• Recordset operations such as FindFirst.

The bug does NOT affect:

• Memo fields.
• Data shown in queries, forms, or reports (unless they rely on recordsets.)
• Results from domain aggregate functions, such as DLookup().

Demonstrating the bug

Download ConcatenatedTextBug.zip (11kb zipped), for Access 2000 and later.

Form1 shows how the concatenated fields are incorrect.

Module1 illustrates the problem using both DAO and ADO.

Avoiding the bug

Unfortunately, this bug is quite pervasive. To be sure you are not bitten, you would need to test every field in each recordset to see if it is a concatenated text field (not memo), or if it includes the result of a function that could yield more than 255 characters. Doing that is probably impractical.

Typical problem scenarios include:

• Any field that relies on a function that could yield more than 255 characters.
• Anything that copies and combines text fields (e.g. boilerplate paragraphs you find in the RecordsetClone of a form, and combine to create a letter.)
• Workarounds for other bugs in Access, such as http://support.microsoft.com/kb/208801

Probable Cause

While we cannot be sure without access to the source code, the bug behaves as if it is a memory assignment error. The characters returned after the 255 are inconsistent: they change as you add objects to your database or move between versions. It looks as if Access thinks 255 characters is enough for a text field when it assigns memory for the field of the recordset, recognises the correct length when it goes to read the value, and therefore returns whatever was in memory after that spot.

This kind of bug has the potential to corrupt a database. If only 255 characters are set aside, but more characters are assigned when the recordset is loaded, is something else being overwritten?

Workaround

You can use a UNION query with a Memo field to coerce Access into treating the concatenated field as a Memo. This yields read-only results, so is not always useful.

The idea is to create a table with a similar structure, but a Memo field where you need the concatenated field. The table has no records. Access looks as the first table in a UNION to determine the data types, so even though this table has no records, it does coerce Access into treating the field as a Memo, and so there is enough memory to handle all characters.

In the example below, the table named StructureOnly has a memo field named MuchText. This query then averts the bug: