FIX: 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

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article has been archived. It is offered "as is" and will no longer be updated.
Symptoms
When you modify a record in a Microsoft Office Access 2003 database table, the record in the linked Microsoft SQL Server 2000 database table is not correctly updated. Instead, the new data is recorded in the wrong record.

This problem occurs when the record that you modify is displayed as part of the recordset that is returned by a query. For example, the record might be displayed in a subform.
Resolution
To resolve this problem, obtain the latest service pack for Office 2003. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
870924 How to obtain the latest service pack for Office 2003
Workaround
To work around this problem, use one of the following methods.

Method 1

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Programmatically select the last record in the recordset and then the first record in the recordset when the recordset is displayed. To do this, use code that is similar to the following example.
Set rs = Forms![Database].Recordsetrs.MoveLastrs.MoveFirstrs.FindFirst ("[Keyfield ID] = " & Me.[Keyfield ID]); Note that "Keyfield ID" is the field name for the key field ; that is used to index the recordset.; Substitute the name of the key field in your; application for "Keyfield ID."
Note This code produces the same results as manually moving the selection to the last record in the recordset and then back to the first record in the recordset.

Method 2

Use an SQL statement instead of an Access 2003 query to create the recordset.
Status
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section. This problem was first corrected in Microsoft Office 2003 Service Pack 2.
Properties

Article ID: 903074 - Last Review: 01/15/2015 17:38:16 - Revision: 3.0

Microsoft Office Access 2003

  • kbnosurvey kbarchive kbqfe kboffice2003sp2fix kbprogramming KB903074
Feedback