Microsoft KB Archive/294842

= PRB: Error When You Update or Delete New Rows in Access 97 Table =

Article ID: 294842

Article Last Modified on 10/17/2001

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q294842



SYMPTOMS
When you use ADO to edit and then update or delete newly added records in a Microsoft Access 97 table, you may receive the following error message when you call the Update method:

Run-time error '-2147217864 (80040e38)':

Row cannot be located for updating. Some values may have been changed since it was last read.

This error message may also occur when you edit or delete newly added records in a DataGrid control. The DataGrid control issues an Update behind the scenes when you move to another row.

NOTE: This error does not occur when you edit or delete existing records.



CAUSE
The Access 97 table contains an AutoNumber field, and the auto-incremented values of newly added records may not be available for the client recordset. For example, if the AutoNumber field is 5 for the new record in the table, the value of this field in the recordset is always 0. ADO uses this value to locate the record when ADO builds an Update Action Query and sends the value to the Microsoft Jet engine to perform the Update on the specified record. However, the Jet engine cannot locate the record based on that value; thus, you receive the above-mentioned error message.



RESOLUTION
The OLE DB Provider for Jet versions 3.51 and 4.0, as well as the Access ODBC drivers 3.51 and 4.0, cannot retrieve the AutoNumber value of newly inserted records from an Access 97 table. This functionality is available only in Microsoft Access 2000 tables. To resolve this problem, upgrade to Access 2000. For more information, see the following Microsoft Web site:

http://www.microsoft.com/office/access/

If you cannot upgrade to Access 2000, avoid using AutoNumber fields.



MORE INFORMATION
ADO builds an Update Action Query based on the edited fields. For example, this query may appear as follows: Update MyTable Set Field1 = &quot;New Value&quot; Where ID = 0

Steps to Reproduce Behavior
NOTE: The following sample allows you to create a table in your Access database. Make sure that you use an Access 97 format database with a table that has an AutoNumber field to reproduce the error.  Open a new Visual Basic Standard EXE project. Set a reference to the Microsoft ActiveX Data Objects Library 2.1 or higher. Place four command buttons on the form, and rename them &quot;cmdCreateTable&quot;, &quot;cmdAddNewRow&quot;, &quot;cmdBindDataGrid&quot; and &quot;cmdExit&quot;. Add DataGrid control from the Components dialog box, and draw an instance on the form.  Copy and paste the following code: Dim rs As New ADODB.Recordset Dim rsGrid As New ADODB.Recordset Dim cn As New ADODB.Connection Dim strSQL As String

Private Sub cmdAddNewRow_Click ' Open recordset in code. rs.CursorLocation = adUseClient rs.Open strSQL, cn, adOpenStatic, adLockOptimistic

' Add new record. rs.AddNew rs(&quot;First_Name&quot;).Value = &quot;MyFirstName&quot; rs(&quot;Last_Name&quot;).Value = &quot;MyLastName&quot; rs.Update ' Edit the same record. rs(&quot;First_Name&quot;).Value = &quot;MyNewFirstName&quot; rs(&quot;Last_Name&quot;).Value = &quot;MyNewLastName&quot; ' Error occurs here. rs.Update

MsgBox &quot;Row Updated with no error&quot; End Sub

Private Sub cmdCreateTable_Click 'Script to create new Access table. cn.Execute &quot;Create Table Table1(ID Identity(1,1) Constraint PrimaryKey Primary Key, &quot; & _              &quot;First_Name Text(20), Last_Name Text(20))&quot;

MsgBox &quot;Table is created.&quot; End Sub

Private Sub cmdExit_Click

' Close and destroy the created objects. If rs.State = adStateOpen Then rs.Close Set rs = Nothing End If   If rsGrid.State = adStateOpen Then rsGrid.Close Set rsGrid = Nothing End If   cn.Close Set cn = Nothing End End Sub

Private Sub Form_Load

cmdCreateTable.Caption = &quot;Create Table&quot; cmdBindDataGrid.Caption = &quot;Bind DataGrid&quot; cmdAddNewRow.Caption = &quot;Add New Row&quot; cmdExit.Caption = &quot;Exit&quot; ' Open ADO Connection. ' IMPORTANT: Modify MyDatabase.mdb here to reflect a valid .mdb file. cn.Open &quot;PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & App.Path & &quot;\MyDatabase.mdb&quot; strSQL = &quot;select * from Table1&quot;

End Sub

Private Sub cmdBindDataGrid_Click

' Open another recordset to bind to the grid. rsGrid.CursorLocation = adUseClient rsGrid.Open strSQL, cn, adOpenStatic, adLockOptimistic ' Set the grid properties. DataGrid1.AllowAddNew = True DataGrid1.AllowDelete = True DataGrid1.AllowUpdate = True

'Bind rdGrid to the DataGrid. Set DataGrid1.DataSource = rsGrid

End Sub   Modify MyDatabase.mdb in the following line: cn.Open &quot;PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & App.Path & &quot;\MyDatabase.mdb&quot; to reflect a valid Access .mdb file. </li> Run the project, and click Create Table if necessary.</li> Click Add New Row. You receive the above-mentioned error message. Click Debug to verify that the error is on the second Update.</li> Click Bind DataGrid, and add a new row. Click another row, and you receive the above-mentioned error message. Also, note that the ID column displays 0. The same error may occur if you try to delete the new row.</li></ol>

If you use an Access 2000 database and repeat these steps, you can add and delete new rows successfully. Also, notice that the ID value displays a value as soon as you move to another row.

<div class="references_section">