I have a SQL Server 2008 R2 database with MS Access front-end (linked ODBC tables). I can see all linked tables in MS Access and can add new rows.
However, when trying to update or delete existing data, I get this error:
Process is stopped by Microsoft Jet because multiple users are trying to edit the same data simultaneously.
There are no multiple users, the table has a primary key on an integer column, and has no bit columns (these all are possible reasons of the similar issues I found on the related forums).
What could be reason of the issue?
I tried to delete and create again linked table in MS Access. Also, I verified that the 'problem' table has primary key over integer column and has no bit columns that could be reason of the issue according to advises on related forums.
There are several common reasons for this.
Most common is a bit (true/false in Access) column that allows nulls. This will trigger the "dreaded" this record been updated by someone else.
The other common issue is the table in question has some kind of delete trigger.
So, to fix this issue:
Make sure the table(s) in question have a PK value. Again, Access allows tables to not have a PK, but linked tables to SQL server require a PK column.
Next up, make sure any/all of the SQL bit columns have a default value of 0 setup, and DO NOT allow nulls for SQL bit columns.
Next up, add a time stamp column to the table(s) in question. Now, while "time stamp" sounds like it has something to do with time, it does not! Hence, the worlds WORST name in history for a column type. The correct "term" is called a row version column, and thankfully over the years, Microsoft is slowly changing the term used for such columns.
So, fix any null bit columns (run an update for any null values to be set = 0). and in SQL table design, set the default value for the bit column to be 0, and not allow nulls.
Add the time stamp column, and then re-link all of your tables.
At this point, you should be able to delete row/records from the Access client.
Since you note no bit columns, the error can still occur due to floating point columns and rounding errors. With such rounding errors, then Access will "think" the record been changed when it has not been. Introduction of a row version (timestamp) column will fix this error, since now Access will NOT resort to a column by column compare of the record to determine if changes have occurred (by you, or someone else). And with floating point rounding errors, then the column by column compare fails. Introduction of a row version column eliminates this column by column compare.
So, introduction of a row version column should fix this error.
Also, avoid using datetime2 columns, and if your table(s) in question have a datetime2 columns, then you need to use the newer ODBC drivers (NC 11 or later).
Note that after adding the timestamp (row version) column, then you need to re-link the tables in Access.