Writing the wrong record (Bookmark bug)

Flaws in Microsoft Access

Provided by Allen Browne, January 2004. Updated October 2005. (Previous version of this page.)

Writing the wrong record (Bookmark bug)

Access can display a record on screen, but if you edit it, your changes overwrite a different record.

This bug occurs in Access 2, 95, and 97. It is partially fixed in Access 97 Service Release 2, but still occurs in that version. It resurfaced in Access 2000. Is it still lurking in later versions?

Access 97 SR-1 and earlier

The underlying cause is Access misinterpreting a form's bookmark after a record has been deleted. The Bookmark is the primary mechanism for finding a record in a form programmatically, e.g. Using a Combo Box to Find Records.

To demonstrate the bug:

1. Open a form that has many hundreds of records in Access 97 SR-1 or earlier.
2. Delete one of the early records.
3. Use RecordsetClone and Bookmark to move to a record more than 262 records further on.
4. Edit the record and save your changes.

At step 3, Access shows on screen the record that you sought. However, the edits at step 4 are written to the following record. If two records are deleted at step 2, the edits are written to two records after the one shown on screen. Clearly, Access is mismatching the form's bookmark.

Access 97 SR-2

The bookmark bug still occurs in Access 97, even with Service Release 2 applied. To demonstrate that the bug is still present, compare the primary key value of the record in the form's Before Update event with the value in its After Update or After Insert event. After a delete, you will still see that Access refers to the wrong record in Form_AfterUpdate and Form_AfterInsert, even though the overwrite of the wrong record no longer occurs with only hundreds of records.

However, if the form has hundreds of thousands of records, Access 97 SR-2 will still overwrite the wrong record. The two databases where we have observed this behaviour both involved attached tables. The bug is still alive.

(Note: Double-check your hard disk does not have multiple copies of msjet35.dll on path. There should be a copy in the windows\system folder. If there is also one in the msaccess.exe folder, or the Program Files folder or the Windows folder, the wrong one may load into memory, yielding the same problems as well SR-2 is not applied.)

Access 2000

As the bug was not fully fixed, it is not surprising that it resurfaced in Access 2000, as described in this article:
    DAO 3.60: Setting Bookmark Property Moves to Wrong Row

As the knowledgebase article explains, a hotfix was made available, and is presumably incorporated in the service packs.

We have not seen the bug in Acc. 2000 SP-3 or later. However, given the seriousness of the bug (overwriting the wrong record), it would be good to know it is fully fixed, and that is clearly not the case, at least with Access 97.

It would be wise to avoid loading large numbers of records into bound forms where deletes are permitted.

Access 2003

Access 2003 can corrupt the data in attached SQL Server 2000 tables in a similar way.

This knowledgebase article was first published in September 2005:
    BUG: A record in a linked SQL Server 2000 database table is not correctly updated when you modify the record in an Access 2003 database table

The article explains that Service Pack 2 for Office 2003 addresses this particular case, but we re-iterate our earlier warning:
    It would be wise to avoid loading large numbers of records into bound forms where deletes are permitted.