Cannot edit or delete rows of linked MS SQL table from MS Access

[efe2023]

Hello Everybody,

There is 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 got error: Process is stopped by Microsoft Jet because multiple users are trying to edit the same data simultaneously. There is no multiple users, also the table has primary key over int column. What can be reason of the issue?

[NeoPa]

Hi Efe.

Welcome to Bytes.com :-)

You say there aren't multiple users, but the system says there are. In this case I'm inclined to trust the system.

Not blindly, as systems can get things wrong. Nevertheless, multiple users means multiple connections or multiple uses across a single connection, and there are many ways a user can have a table/record open multiple times without realising it.

Without knowing all the details I cannot determine for you what the issue is, but here are some considerations and ideas that may help you find your way :

1. Try restarting your system.
   Sometimes processes that you think have terminated, or even those you were never aware of, have connections open.
2. Try your test again as soon as you can after restarting.
   That will make it less likely that other processes can get in the way.
3. Check out the driver you're using. Is it the most up-to-date ODBC driver?
4. If you have access to the data directly, via SSMS (SQL Server Management Studio) for instance, or know of someone who has such access and can help you, see if you can edit the data directly from there.
   Be sure to close down any open tables there before trying again from Access.

That's pretty-much all I can suggest for now, but good luck & please let us know if you make any progress.

[isladogs]

Bear in mind that the other user(s) could be yourself trying to change data in more than one way at the same time
This can often lead to a write conflict error

[jimatqsi]

efe2023,
There's a very good chance that the problem stems from bit fields (yes/no fields in Access) that have null values. Try this. Create a query that pulls a single string or numeric field. Try to edit the field. If it works, expand your list of fields returned by the query, excluding yes/no columns. You will probably find that you can edit those fields.

To solve the problem in a way that allows you to query and edit the bit fields, you will need to do some SQL coding. You can do it in SQL Server Management System (SSMS) or in a passthrough query. What you need to to do is update any null value in a bit field to be either 0 or 1. You'll have to choose which is the best option for each column that needs this fix.

Here's some sample code, but you'll need to substitute the table and field names as needed.

UPDATE [dbo].[Customer]
   SET [Active] = 0
 WHERE ACTIVE=NULL

If you're not familiar with SSMS, just create a pass-through query with the same connection string you use in your ODBC table connections.

Jim