PRB: Error When You Update or Delete New Rows in Access 97 Table (294842)

MORE INFORMATION

Update MyTable Set Field1 = "New Value" Where ID = 0
				

Steps to Reproduce Behavior

1. Open a new Visual Basic Standard EXE project.
2. Set a reference to the Microsoft ActiveX Data Objects Library 2.1 or higher.
3. Place four command buttons on the form, and rename them "cmdCreateTable", "cmdAddNewRow", "cmdBindDataGrid" and "cmdExit".
4. Add DataGrid control from the Components dialog box, and draw an instance on the form.
5. 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("First_Name").Value = "MyFirstName"
       rs("Last_Name").Value = "MyLastName"
       rs.Update
       
       ' Edit the same record.
       rs("First_Name").Value = "MyNewFirstName"
       rs("Last_Name").Value = "MyNewLastName"
       
       ' Error occurs here.
       rs.Update
   
       MsgBox "Row Updated with no error"
       
   End Sub
   
   Private Sub cmdCreateTable_Click()
       
       'Script to create new Access table.
       cn.Execute "Create Table Table1(ID Identity(1,1) Constraint PrimaryKey Primary Key, " & _
                  "First_Name Text(20), Last_Name Text(20))"
   
       MsgBox "Table is created."
       
   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 = "Create Table"
       cmdBindDataGrid.Caption = "Bind DataGrid"
       cmdAddNewRow.Caption = "Add New Row"
       cmdExit.Caption = "Exit"
       
       ' Open ADO Connection.
       ' IMPORTANT: Modify MyDatabase.mdb here to reflect a valid .mdb file.
       cn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\MyDatabase.mdb"
       
       strSQL = "select * from Table1"
   
   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
   					

6. Modify MyDatabase.mdb in the following line:

   cn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\MyDatabase.mdb"

   to reflect a valid Access .mdb file.
7. Run the project, and click Create Table if necessary.
8. Click Add New Row. You receive the above-mentioned error message. Click Debug to verify that the error is on the second Update.
9. 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.