IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Identified by Henrik Bechmann. Provided by Allen Browne, December 2006. Updated July 2008.
If you combine text fields, any characters after the first 255 are garbled when you open a Recordset.
The bug affects:
The bug does NOT affect:
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.
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:
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?
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:
|Home||Index of tips||Top|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'Concatenated fields yield garbage in recordset'?
No comments yet.
Why not be the first to comment on this article?!
Have your say...