Microsoft KB Archive/323201

= ACC2000: &quot;#Deleted&quot; Appears in All Fields After Inserting Record with Default Value in Primary Key Field =

Article ID: 323201

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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 &quot;#Deleted&quot; 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 &quot;Steps to Reproduce the Behavior&quot; 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 &quot;#Deleted&quot; nor do you receive the error message that was mentioned in the &quot;Symptoms&quot; 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.



Steps to Reproduce the Behavior
 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

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

Specify Northwind as the database to connect to.</li> Link to Dbo.tblTest, and then click to select the Save Password check box.</li> Open the linked table, dbo_tblTest, in Datasheet view.</li> Type any value in the txtTest field, and then press the DOWN ARROW key to move to the next record.</li></ol>

Note that you see &quot;#Deleted&quot; 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.

<div class="references_section">