Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

This article was previously published under Q264766

↑ Back to the top


Symptoms

When you insert a new record into a linked ODBC table that has a fixed-length text field (CHAR) as the primary key, #DELETED appears in all of the fields in the new record.

Note If you then close and reopen the linked table, the newly inserted record appears and is properly inserted.

↑ Back to the top


Cause

Many ODBC drivers add spaces to (or "pad out") a CHAR field when the number of characters that are inserted is less than the maximum length of the field. Microsoft Jet 4.0 incorrectly reads back the inserted, padded value and compares it with the original, unpadded value. Because the two values are different, Jet reports to Microsoft Access that the newly inserted record cannot be found.

↑ Back to the top


Resolution

To resolve this problem, install the latest Jet 4.0 service pack. 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

↑ Back to the top


Workaround

Use a variable-length text field (VARCHAR) as the primary key for the table.

↑ Back to the top


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 fixed in Jet 4.0 Service Pack 6 (SP6).

↑ Back to the top


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.

↑ Back to the top


Keywords: KB264766, kbjet, kbfix, kbdatabase, kbbug, kbhotfixserver, kbqfe

↑ Back to the top

Article Info
Article ID : 264766
Revision : 4
Created on : 4/7/2006
Published on : 4/7/2006
Exists online : False
Views : 481