Microsoft KB Archive/251289

= ACC2000: New Row in Linked SQL Server Table Disappears or Appears as a Duplicate of Last Record =

Article ID: 251289

Article Last Modified on 7/15/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q251289



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
When you add a new record to a linked Microsoft SQL Server table and then move to a different record, you may notice one of the following symptoms:

When you press the TAB key to move to the next new record after entering any part of a new record in a table, the record you just typed disappears.When you type a complete new record in a table and then move to an existing record, the record you typed disappears.When you type only part of a new record into a table and then move off that record to an existing record, your new record disappears, and the last record in the table appears to be duplicated.When you type any part of a record in a form, and then leave the record, the information you type disappears.

IMPORTANT: Data is not actually lost. Do not try to add the record again; it does exist. Do not try to delete the apparent duplicate record. By deleting it, you delete the original record, which appears to be duplicated, not the record you just added.



CAUSE
If the SQL Server table has an identity column, when you try to add a record, you instead see what appears to be a duplicate of the previous record. The new record does not display the correct data until you refresh the recordset. This behavior is related to the way Microsoft Access requeries the SQL Server database.



RESOLUTION
To resolve this issue, obtain the latest Microsoft Jet 4.0 service pack update. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

239114 How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine

To work around this problem, use any of the following three methods.

Using an Access Project
You can use an Access project instead of an Access database. By using an Access project, you do not need to have linked tables to connect to a SQL Server database.

For additional information about converting your Access database to an Access project, click the article numbers below to view the articles in the Microsoft Knowledge Base:

241743 ACC2000: "Access 2000 Upsizing Tools" White Paper Available in Download Center

250616 ACC2000: How to Use DTS to Export Data from a Microsoft Access Database to a SQL Server Database

Using Forms in an Access Database
If you see this behavior in a form, use Microsoft Visual Basic for Applications code for the BeforeInsert and AfterInsert events to automatically requery the data and move the form to the newly added record. With the sample code below, you can add to the events to automatically refresh the record on the form.

NOTE: With this method you may notice you have to move off a new record twice. That is, when you add a new record and then press the TAB key or one of the record navigation buttons to add another new record or move to the previous record, you have to press the record navigation button a second time to get to the appropriate record. This does not affect closing the form. Option Compare Database Option Explicit

Dim mfRequery As Boolean

Private Sub Form_AfterInsert

If mfRequery = True Then ' If the variable indicates a Requery ' is needed, Requery the form. Me.Requery ' Move back to the record that was just added DoCmd.GoToRecord acDataForm, Me.Name, acLast mfRequery = False End If

End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)

If Me.NewRecord = True Then ' If this is a new record, set a variable ' to indicate the need to Requery. mfRequery = True End If End Sub

Using Tables in a MDB
If you see this behavior in a table, you can manually resort the table or close and reopen the table.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was corrected in the latest Microsoft Jet 4.0 service pack.



Steps to Reproduce Behavior

 * 1) Open the sample database Northwind.mdb.
 * 2) Export the Orders table to an existing SQL Server database. Name it OrdersTest.
 * 3) Create a new Access database.
 * 4) In the new database, create a new linked table to the OrdersTest table you exported to SQL Server.
 * 5) After linking to it, open the table.
 * 6) Scroll down to the last record.
 * 7) At the new record line, type in a new record.
 * 8) After typing in all the data, press the UP ARROW key to move the cursor out of the new record.
 * 9) You see the new record is no longer showing, but the last record appears twice.
 * 10) On the Records menu, click Requery. You notice the duplicate is gone.

Additional query words: prb attached 6.5 7.0 refresh down

Keywords: kbprb kbpending KB251289

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.