This appears to be a very common error message, but I have been unable to locate a solution in any previous response.
I have an ODBC linked table in an Access MDB, to a SQLServer 2005 table which has a few columns including an identity int which is the primary key. Very ordinary - sample structure attached.
If, using ADO against the MDB, I execute a basic update statement, such as:
UPDATE TestTable SET Description= ABC WHERE ID = 2
I receive the error as titled.
Attempting the same thing from inside MS Access gives the &Microsoft Office Access didnt update& 1 record(1) due to lock violations&. error.
The statement works fine when run directly against SQL Server and the table can be browsed and updated via the table view in Access.
This error has appeared in a previously stable environment for no apparent reason. If the database is restored onto a SQL2005 Express instance on another machine, the update is successful (no error).
The MDB is not corrupt as it has been newly created from Access.
Using profiler shows (from what I can tell) that the update transaction is rolled back instead of committed.
So what is access/ODBC detecting on my server that causes the unusual sequence? For all other purposes the server appears to be working normally.
CREATE TABLE [dbo].[TestTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](100) NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED