The Microsoft Jet Database engine stopped the process because you and another user are attempting to change the same data at the same time

Dealer_Solutions
Dealer_Solutions used Ask the Experts™
on
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 
([Id] ASC))

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
The problem is on the Access side.  A sure-fire way of recreating is to change some of the database fields in your form, then kick off VBA code that changes other fields (for example, in an after update event).  Will always happen when you hit the update statement.

The way I generally deal with this is by saving the record immediately before making modifications to the record at the VBA level.
The cause of this bug is the default connection options as configured on the server.  If the No Count option is set, the error is returned after an ADO update.

From 2005 BOL: Turns off the message returned at the end of each statement that states how many rows were affected.

Grrr, microsoft (and whoever changed the setting on my server...)

Commented:
Hi Dealer_Solutions,

Add a new field to TestTable of type TimeStamp

Gary

Commented:
Did you try my suggestion.? .It's worked for me many times!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial