Updates to Records not allowed - Write Conflict

Question

This stems from a previous question I asked - about a write conflict with a form, but the problem seems to be originating from the fact that I can update existing records in a linked table provided by one System DSN, but not another linked table provided by another DSN (different database) - allowing me to enter records at first, but then preventing me from making any changes to the records as if another user has changed the data before me. There are no check constraints defined on the server for default values, no triggers, no views, no other users accessing the same table.

Both DSNs are set up IDENTICAL except for the default database they open - the first opening gtdata_test, while the second one opens instkeeper_test. Both databases on the SQL Server instance are owned by me, I am the only logged in user.

I have tested this, and this problem occurs at the table level (no VBA in the program written, nothing) with manual updates.

In case it helps, I will include the CREATE statements for the good table, and for the affected tables below it. I hope someone can help me, as I am fresh out of ideas.

*Code to create Supplier_Master table in gtdata_test*:

USE [gtdata_test]
GO

/****** Object:  Table [dbo].[Supplier_Master]    Script Date: 05/27/2009 15:58:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Supplier_Master](
    [Supplier_Code] [nvarchar](50) NOT NULL,
    [Supplier_Master_Name] [nvarchar](50) NULL,
    [Salutation] [nvarchar](50) NULL,
    [Contact] [nvarchar](50) NULL,
    [Phone] [nvarchar](50) NULL,
    [Fax] [nvarchar](50) NULL,
    [EMail] [nvarchar](50) NULL,
    [Address] [nvarchar](50) NULL,
    [City] [nvarchar](50) NULL,
    [State] [nvarchar](50) NULL,
    [Zip] [nvarchar](50) NULL,
    [Country] [nvarchar](50) NULL,
    [Last_Review] [datetime] NULL,
    [Last_Rating] [datetime] NULL,
    [Last_Received] [datetime] NULL,
    [Last_Reject] [datetime] NULL,
    [Enabled] [int] NULL,
    [User1] [nvarchar](50) NULL,
    [User2] [nvarchar](50) NULL,
    [SupType] [nvarchar](50) NULL,
 CONSTRAINT [Supplier_Master$PrimaryKey] PRIMARY KEY CLUSTERED 
(
    [Supplier_Code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$Address$disallow_zero_length] CHECK  ((len([Address])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Address$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$City$disallow_zero_length] CHECK  ((len([City])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$City$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$Contact$disallow_zero_length] CHECK  ((len([Contact])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Contact$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$Country$disallow_zero_length] CHECK  ((len([Country])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Country$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$EMail$disallow_zero_length] CHECK  ((len([EMail])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$EMail$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$Fax$disallow_zero_length] CHECK  ((len([Fax])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Fax$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$Phone$disallow_zero_length] CHECK  ((len([Phone])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Phone$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$Salutation$disallow_zero_length] CHECK  ((len([Salutation])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Salutation$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$State$disallow_zero_length] CHECK  ((len([State])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$State$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$Supplier_Code$disallow_zero_length] CHECK  ((len([Supplier_Code])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Supplier_Code$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$Supplier_Master_Name$disallow_zero_length] CHECK  ((len([Supplier_Master_Name])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Supplier_Master_Name$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$SupType$disallow_zero_length] CHECK  ((len([SupType])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$SupType$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$User1$disallow_zero_length] CHECK  ((len([User1])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$User1$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$User2$disallow_zero_length] CHECK  ((len([User2])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$User2$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$Zip$disallow_zero_length] CHECK  ((len([Zip])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Zip$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master] ADD  DEFAULT ((0)) FOR [Enabled]
GO

*Code to create tblSupplierInfo in instkeeper_test*:

USE [instkeeper_test]
GO

/****** Object:  Table [dbo].[tblSupplierInfo]    Script Date: 05/27/2009 15:57:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblSupplierInfo](
    [strSupplierID] [nvarchar](50) NOT NULL,
    [bolSupAltShipAddyRep] [bit] NULL,
    [bolSupAltShipAddyCal] [bit] NULL,
    [bolSupInsistNet30] [bit] NULL,
    [bolRMARequireRepair] [bit] NULL,
    [bolRMARequireCalibration] [bit] NULL,
    [bolSupShipOrCourier] [bit] NULL,
    [bolSupRequireMSDS] [bit] NULL,
    [bolSupBlanketPO] [bit] NULL,
    [bolSupRequirePricing] [bit] NULL,
    [bolSupBlankPricing] [bit] NULL,
    [bolSupFaxPOSend] [bit] NULL,
    [bolAdditionalPaperworkRepair] [bit] NULL,
    [bolAdditionalPaperworkCalibration] [bit] NULL,
    [strRMARepairWordage] [nvarchar](100) NULL,
    [strRMACalibrationWordage] [nvarchar](100) NULL,
    [intBlanketPO] [int] NULL,
    [bolUseFedExNumber] [bit] NULL,
    [strFedExNumber] [nvarchar](150) NULL,
    [bolUseUPSNumber] [bit] NULL,
    [strUPSNumber] [nvarchar](150) NULL,
    [bolSupA2LAAccredited] [bit] NULL,
    [bolSupFreightAllow] [bit] NULL,
    [bolSupFreightOnly] [bit] NULL,
    [bolSupUseMiscNum] [bit] NULL,
    [strSupMiscFreightNum] [nvarchar](150) NULL,
 CONSTRAINT [tblSupplierInfo$PrimaryKey] PRIMARY KEY CLUSTERED 
(
    [strSupplierID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

*Code to create tblSupplierNote in instkeeper_test*:

USE [instkeeper_test]
GO

/****** Object:  Table [dbo].[tblSupplierNote]    Script Date: 06/01/2009 12:34:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblSupplierNote](
    [intSupNoteID] [int] IDENTITY(1,1) NOT NULL,
    [strSupplierID] [nvarchar](50) NULL,
    [datDateNoteEntered] [datetime] NULL,
    [datTimeNoteEntered] [datetime] NULL,
    [strNoteBy] [nvarchar](255) NULL,
    [memSupNote] [nvarchar](max) NULL,
 CONSTRAINT [tblSupplierNote$PrimaryKey] PRIMARY KEY CLUSTERED 
(
    [intSupNoteID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblSupplierNote]  WITH CHECK ADD  CONSTRAINT [tblSupplierNote$tblSupplierInfotblSupplierNote] FOREIGN KEY([strSupplierID])
REFERENCES [dbo].[tblSupplierInfo] ([strSupplierID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[tblSupplierNote] CHECK CONSTRAINT [tblSupplierNote$tblSupplierInfotblSupplierNote]
GO

Just to overview - any table I pull into the application I am developing using the first DSN are read/write-able, records can be created, removed, and updated without any problem. With the second DSN, any table I pull into the application can be written to ONCE. Any further changes are lost, and a Write Conflict dialog box appears whenever data is updated in the table in datasheet view.

Software I am using:

• Microsoft Access 2003
• SQL Server Express 2008
• Windows XP Professional SP3

-- Edited 06/03/2009 @ 1307 hours --

I found out that a strange problem that crops up when using Yes/No checkboxes and SQL Server with Access. Apparently, Access will interpret NULL as No - changing the value, but SQL Server will not interpret NULL as a No in a Bit field (what Yes/No gets turned into in conversion) so it throws a Write Conflict error when a value is not required, and is NULL. The solution was to redesign the table so that a value was required, and that there was a default value assigned for EVERY former Yes/No checkbox. This solved the mysterious Write Conflict messages, and allowed changes to records once they were created.

Answer 1

Have you tried deleting your table links and recreating them? Changes to the tables/views that you are linking to can cause them to become read-only and there's no way to correct it except to delete them and recreate them.

Also, I link using the DSN, but then use Doug Steele's code to convert them to DNS-less connection strings.

Answer 2

There is a difference between how Access handles Yes/No checkbox values and SQL Server. When translating Yes/No booleans from Access to SQL Server, you must remember to define a default state as well as mark it as requiring an answer. Otherwise, you will get write conflicts every time, and it will prevent the record from being saved with your changes once the initial values have been set.

Answer 3

I've had similar "Write Conflict" with Access front-end to SQL Server when records contain NULL bit datatype column(s), which I notice your table contains, even if you're not updating those columns. Setting bit datatype columns to default of 0 and updating existing records with bit datatype columns of NULL to 0 has fixed for me.