473,225 Members | 1,599 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,225 software developers and data experts.

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

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?
Dec 22 '23 #1
3 26425
NeoPa
32,554 Expert Mod 16PB
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.
Dec 22 '23 #2
isladogs
451 Expert Mod 256MB
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
Dec 24 '23 #3
jimatqsi
1,271 Expert 1GB
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.
Expand|Select|Wrap|Line Numbers
  1. UPDATE [dbo].[Customer]
  2.    SET [Active] = 0
  3.  WHERE ACTIVE=NULL
  4.  
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
Dec 26 '23 #4

Sign in to post your reply or Sign up for a free account.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.