Why #Deleted in records from linked table?

Question

My table is on a SQL Server as defined below. When the Unique Constraint is added to the table, the Access data entry form displays #Deleted in all fields of a record that was just entered. I can get around the situation by requerying and forcing a new record. There are usually only 1 - 3 records per user.

Requery
DoCmd.GoToRecord , , acNewRec

Data Entry Form

Why does adding the Unique Constraint interfere with the Access form? The form works fine without the unique constraint.

CREATE TABLE [dbo].[tbl_INV_cust_inv_us_temp] (
  [inv_loc]          [char](3)          NOT NULL,
  [inv_no]           [char](7)          NOT NULL,
  [std_GUID]         [uniqueidentifier] NOT NULL,
  [std_NewRecordInd] [bit]              NOT NULL,
  [std_Create_TS]    [datetime]         NOT NULL,
  [std_Create_ID]    [varchar](50)      NOT NULL
); 
GO

ALTER TABLE [dbo].[tbl_INV_cust_inv_us_temp] 
  ADD CONSTRAINT [DF_tbl_INV_cust_inv_us_temp_std_GUID]  
  DEFAULT (newid()) FOR [std_GUID];
GO

ALTER TABLE [dbo].[tbl_INV_cust_inv_us_temp] 
  ADD CONSTRAINT [DF_tbl_INV_cust_inv_us_temp_std_NewRecordInd]  
  DEFAULT (1) FOR [std_NewRecordInd];
GO  

ALTER TABLE [dbo].[tbl_INV_cust_inv_us_temp] 
  ADD CONSTRAINT [DF_tbl_INV_cust_inv_us_temp_std_Create_TS]  
  DEFAULT (getdate()) FOR [std_Create_TS];
GO

ALTER TABLE [dbo].[tbl_INV_cust_inv_us_temp] 
  ADD CONSTRAINT [DF_tbl_INV_cust_inv_us_temp_std_Create_ID]  
  DEFAULT (right(suser_sname(),(len(suser_sname()) - 9))) FOR [std_Create_ID];
GO

ALTER TABLE [dbo].[tbl_INV_cust_inv_us_temp] 
  ADD CONSTRAINT [IX_tbl_INV_cust_inv_us_temp] 
  UNIQUE NONCLUSTERED (
    [inv_loc] ASC,
    [inv_no] ASC,
    [std_Create_ID] ASC) 
  WITH (
    PAD_INDEX  = OFF, 
    STATISTICS_NORECOMPUTE  = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS  = ON, 
    ALLOW_PAGE_LOCKS  = ON )
  ON [PRIMARY];
GO

Just wondering: why is this a UNIQUE constraint and not a PRIMARY KEY? Access (more or less) requires a PK in a linked ODBC table in order to work with it. — Andre
I'm not sure here - but with Unique index that includes inv_loc and inv_no - and theose fields defined as NOT NULL, how are you loading them with a new record if there's no defaults set? — dbmitch
Like many posts, I inherited this mess. There were actually PRIMARY KEY (PK) on std_GUID and UNIQUE (U) constraints on the table. I tried the four combinations of constraints: 0,0 - OK; 0,U - fails; PK,0 - OK; PK,U - fails. I realize this is functionally redundant. The primary key provides record uniqueness for lookups, while the unique constraint provides that the user not enter duplicate records. Access treats the UNIQUE constraint like a PK, and does not give you a dialog box to choose a PK when linking the table. Records are input through the Access data entry form (link above). — David Dubs

Christopher Brown Christopher Brown · Accepted Answer · 2016-07-07T15:40:13

Per this article, the unique fields within a table are the "key" Access uses to retrieve and update records via an ODBC connection.

Specifically, your issue is related to the std_Create_ID. Your default value for std_Create_ID is being created on the backend using the suser_sname() function. So, from Access' perspective, the std_Create_ID field is an empty string (or maybe null) when it creates the record and so the unique key is a combination of [inv_loc], [inv_no], and an empty string (or maybe a null). However, as soon as the record is created, the combination of those three variables no longer exists, and so it assumes the record has been deleted, per the article.

Based on the article and the way you're doing things here, this can't be fixed with your current setup. You said you inherited all of this, so I'm not sure what you can change. However, I see several options:

Continue with your workaround (i.e., requerying)
Build the std_Create_ID in Access (which likely isn't possible).
Remove std_Create_ID from the unique constraint on the table.

I hope this helps.

Why #Deleted in records from linked table?

1 Answers