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 (903074)



The information in this article applies to:

  • Microsoft Office Access 2003

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].Recordset

rs.MoveLast
rs.MoveFirst

rs.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.

Modification Type:MinorLast Reviewed:7/28/2006
Keywords:kbQFE kbOffice2003SP2fix kbProgramming kbbug KB903074 kbAudDeveloper