FIX: #DELETED Error When Inserting Records with CHAR Primary Key (264766)

MORE INFORMATION

Steps to Reproduce Behavior

You can reproduce this problem by linking to a DB2, Oracle, or Microsoft SQL Server database. The problem does not seem to occur unless you have at least 60 or more records in the linked table.

If you examine an ODBC trace taken during the insert operation, you will see that Jet inserts and reselects the record properly, and that no errors occur in the trace. The problem occurs after Jet retrieves the record, and attempts to compare the reselected key value with the value that was entered by the user.

To reproduce this behavior, perform the following steps:

1. Run the following Microsoft Visual Basic code to generate the test table and records:

   Sub GenerateTestTable()
   Dim conn As Object
   Dim i As Long, sql as String
      Set conn = CreateObject("adodb.connection")
      ' Change connection string to point to your SQL Server if needed.
      conn.Open "Provider=SQLOLEDB;Server=(Local);Database=Pubs;UID=sa;PWD=;"
      On Error Resume Next
      conn.Execute "drop table tmp_chartest"
      On Error GoTo 0
      sql = "create table tmp_chartest(f1 char(5) primary key, f2 char(10))"
      conn.Execute sql
       For i = 1 To 70
           sql = "insert into tmp_chartest (f1,f2) values ('"
           sql = sql & Format(i, "000") & "','XXXXXXXXXX')"
           conn.Execute sql
       Next i
       conn.Close
       Set conn = Nothing
   End Sub
   					

2. Start Microsoft Access 2000, and link to the tmp_chartest table that was created in the previous step.
3. Open the newly-linked table, and scroll to the last record in the table.
4. Add a new record with field F1 = 071 and field F2 = XXXXXXXXXX.
5. Tab off the record, and you should see #DELETED displayed in all of the fields.