I have a fairly large ERP-system that I'm maintaining for a company. It's a MS Access front end with a SQL database. No issues so far.
Now I'm setting up a new table, like I have done numerous times in this system (and others), but now I'm running into this problem:
My new table allows inserts, but inserted records cannot be updated or deleted. That is, some records... I've never run into this before, and this table is nothing spectacular
The error (in dutch) states that the record has been changed by another user and therefore my changes cannot be applied (which is impossible since there are no other users on the developmentserver)
Even more strange: I cannot update or delete the first record, but I can alter the second record. This seems to be random.
I'm familiar with the following:
- a table needs a primary index key to function properly: this table has such a field
- SQL bit fields need to have a default (0 or 1) to prevent these warnings for MS Access boolean: this table does not have bitfields
- tabletriggers that write in the table after an update (through MSAccess) cause the table to be temporarily locked with the same error as above: no triggers on the table
Also
- I've recreated the table in various versions (omitting specific fields and options like constraints, but so far no luck)
- I've tried different drivers (SQL Server, ODBC Driver for SQL 17, SQL Server Native Client 11), all have the same issue
- every time I recreate the table I also create a new linked table, the tables are linked using a DSN-less connectionstring
I must be overlooking something very simple, but I cannot find it. Been into this issue for a day now and I'm getting really frustrated
This is my table, as you can see I've already omitted several options from the tabledefiniton, but the problem stays. Things like the auditfields (Created, Updated) are in all other tables the same way, no issues)
Now I'm setting up a new table, like I have done numerous times in this system (and others), but now I'm running into this problem:
My new table allows inserts, but inserted records cannot be updated or deleted. That is, some records... I've never run into this before, and this table is nothing spectacular
The error (in dutch) states that the record has been changed by another user and therefore my changes cannot be applied (which is impossible since there are no other users on the developmentserver)
Even more strange: I cannot update or delete the first record, but I can alter the second record. This seems to be random.
I'm familiar with the following:
- a table needs a primary index key to function properly: this table has such a field
- SQL bit fields need to have a default (0 or 1) to prevent these warnings for MS Access boolean: this table does not have bitfields
- tabletriggers that write in the table after an update (through MSAccess) cause the table to be temporarily locked with the same error as above: no triggers on the table
Also
- I've recreated the table in various versions (omitting specific fields and options like constraints, but so far no luck)
- I've tried different drivers (SQL Server, ODBC Driver for SQL 17, SQL Server Native Client 11), all have the same issue
- every time I recreate the table I also create a new linked table, the tables are linked using a DSN-less connectionstring
Code:
ODBC;Driver={SQL Server Native Client 11.0};SERVER=XXXXXXXX\SBDEV;DATABASE=Dev20032;Trusted_Connection=Yes;APP=Microsoft Office 2010I must be overlooking something very simple, but I cannot find it. Been into this issue for a day now and I'm getting really frustrated
This is my table, as you can see I've already omitted several options from the tabledefiniton, but the problem stays. Things like the auditfields (Created, Updated) are in all other tables the same way, no issues)
SQL:
IF OBJECT_ID(N'dbo.tblOrdOutputCfg', N'U') IS NOT NULL
DROP TABLE [dbo].[tblOrdOutputCfg];
GO
CREATE TABLE [dbo].[tblOrdOutputCfg](
[OrdOutputCfgID] [int] IDENTITY(1,1) NOT NULL,
[OrdOutputCfgOrdID] [int] NOT NULL,
[OrdOutputCfgOutPutNr] [int] NULL,
[OrdOutputCfgQltyID] [int] NULL,
[OrdOutputCfgSzID] [int] NULL,
[OrdOutputCfgSilos] [nvarchar](255) NULL,
--[OrdOutputCfgOutputGew] [decimal](10, 2) NULL,
[OrdOutputCfgCreatedOn] [datetime] NULL,
[OrdOutputCfgCreatedBy] [nvarchar](50) NULL,
[OrdOutputCfgUpdatedOn] [datetime] NULL,
[OrdOutputCfgUpdatedBy] [nvarchar](50) NULL,
CONSTRAINT [PK_tblOrdOutputCfg] PRIMARY KEY CLUSTERED
(
[OrdOutputCfgID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblOrdOutputCfg] ADD CONSTRAINT [DF_tblOrdOutputCfg_OrdOutputCfgCreatedOn] DEFAULT (getdate()) FOR [OrdOutputCfgCreatedOn]
GO
--ALTER TABLE [dbo].[tblOrdOutputCfg] ADD CONSTRAINT [DF_tblOrdOutputCfg_OrdOutputCfgCreatedBy] DEFAULT ([dbo].[svfUsername]()) FOR [OrdOutputCfgCreatedBy]
--GO
--ALTER TABLE [dbo].[tblOrdOutputCfg] WITH CHECK ADD CONSTRAINT [FK_tblOrdOutputCfg_tblOrd] FOREIGN KEY([OrdOutputCfgOrdID])
--REFERENCES [dbo].[tblOrd] ([OrdID])
--ON DELETE CASCADE
--GO
--ALTER TABLE [dbo].[tblOrdOutputCfg] CHECK CONSTRAINT [FK_tblOrdOutputCfg_tblOrd]
--GO
