ACC2000: "#Deleted" Appears in All Fields After Inserting Record with Default Value in Primary Key Field (323201)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q323201
Advanced: Requires expert coding, interoperability, and multiuser skills.

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

SYMPTOMS

When you insert a record into a linked Microsoft SQL Server table where the primary key of the table contains at least one field that is populated by using a default value, the values of the record appear as "#Deleted" until you close and then you reopen the linked table.

You may also receive the following error message:
ODBC--Call Failed
[Microsoft][ODBC SQL Server Driver] Invalid character value for cast specification.

CAUSE

This is a known problem with Access. The problem is related to default values that are set in the back-end schema of the linked table.

RESOLUTION

To work around this problem you can use either of the following methods:

Method 1

Use a form to insert new records to the linked SQL Server table. Set the Default Value property of the form control for the field that contains the default value and that serves as a field in the primary key. The following steps walk you through an example of creating a form that is based on the linked table that is created in the "Steps to Reproduce the Behavior" section:
  1. In the Database window, click Tables under Objects, and then click to select dbo_tblTest.
  2. On the Insert menu, click AutoForm.
  3. Save the form as frmTest.
  4. Open frmTest in Design view.
  5. Double-click the txtCountry text box to bring up the property sheet of the control.
  6. In the property sheet, click the Data tab.
  7. Enter USA in the Default Value property.
  8. Save and then close frmTest.
  9. Open frmTest in Form view. Try to enter data in the fields. You no longer see "#Deleted" nor do you receive the error message that was mentioned in the "Symptoms" section.

Method 2

Remove the field that contains a default value from the primary key on the SQL Server table.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Use Query Analyzer to run the following script on SQL Server 2000:

    Use Northwind
    Go

    CREATE TABLE [dbo].[tblTest] (
    [intID] [int] IDENTITY (1, 1) NOT NULL ,
    [txtCountry] [varchar] (3) NOT NULL ,
    [txtTest] [varchar] (7) NOT NULL ,
    ) ON [PRIMARY]
    GO

    CREATE DEFAULT usa_const AS 'usa'
    GO

    sp_bindefault usa_const, 'tblTest.txtCountry'
    GO

    ALTER TABLE [dbo].[tblTest] WITH NOCHECK ADD
    CONSTRAINT [PK_ID_Country] PRIMARY KEY NONCLUSTERED
    (
    [intID], [txtCountry]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO

  2. Open Access, and then create a new blank database.
  3. In the Database window, click Tables under Objects, and then click New.
  4. In the New Table dialog box, click Link Table, and then click OK.
  5. From the Files of type list, click ODBC Databases.
  6. Create a new System DSN pointing to your SQL Server.

    Specify Northwind as the database to connect to.
  7. Link to Dbo.tblTest, and then click to select the Save Password check box.
  8. Open the linked table, dbo_tblTest, in Datasheet view.
  9. Type any value in the txtTest field, and then press the DOWN ARROW key to move to the next record.
Note that you see "#Deleted" for all fields in the record that you just tried to enter. If you close and then you reopen the table, the record that you entered now shows the correct values.

REFERENCES

For additional information about problems adding records to a SQL Server table with default values, click the article numbers below to view the articles in the Microsoft Knowledge Base:

279888 BUG: Error When You Use Client Cursor to Add Record to SQL Server Table That Has Default Value in Datetime Field

208799 ACC2000: New SQL Records Appear Deleted Until Recordset Reopened

Modification Type:MajorLast Reviewed:6/28/2004
Keywords:kbbug kbpending KB323201
©2004 Microsoft Corporation. All rights reserved.