[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

MS Access 2003 - Update/Delete fails with Write conflict/clipboard error

Hi,

I'm afraid this problem has been mentioned more than once on the experts-exchange before.
I tried all suggestions for solutions that I could find, but I can't seem to fix the problem:
I just upgrade my MS SQL from MSDE to MSSQL 2005, and a frontend created in Ms Access 2003, connecting to the database using ODBC connection.
I am using SBS 2003 for the Server, and WinXp for the workstations (10 PC)

 I can added new records with no problem to the table via the form, but if I try to update or delete existing records, I get the error:
Write conflict:
This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.
Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.

This problem just happened after using MS SQL 2005, and only a few computers have this problem.
I checked all the MS Access references, the version for the OS + MS Office. All are the same between the workstation that have a problem and the one that working.

Any assistance greatly appreciated!

Cheers and thanks,

Josh
0
kmsnz
Asked:
kmsnz
  • 4
  • 4
1 Solution
 
slamhoundCommented:
This is usually because two "things" have written to the current record. For instance, if the user makes a change and your code makes a change, Access can treat it as two different users and so make you choose who gets to commit the change. This can also happen if something on the SQL server writes to the record (identity field, trigger, stored procedure etc) while the user is editing the data.

for that matter, you may be supplying an autonumber field and SQL is supplying an ident for the same field.

have a look around and see what you can find.
0
 
kmsnzAuthor Commented:
Hi Slamhound,

Thanks a lot for your reply.
About the autonumber, I have an autonumber field for the ID.
I copied the frontend from one PC to another PC. And from 10 workstations only 3 got that problem (can add a new record  but can not edit). also basically just one user accessing the database. That 3 computers/ws always have a problem when editing the record but the rest no problem at all. So I just wondering is it because of the software issue?
If the problem with the MS Access form or MS SQL script why only happened on that 3 particular computers/ws only.

Cheers,

Josh
0
 
slamhoundCommented:
If you put a different user at thoes computers, does this issue still happen? I'm wondering if it's the way thoes 3 particular users are entering the data.

Also, make sure thoes 3 computers have no firewall, virus checker or any other security device that may be affecting things. (you can turn these back on once you work out wich is causing the issue) Also, make sure that these 3 computers have the latest patches from Microsoft and the same versions of all other software. If there is other software running in the background, try disabling that too.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
kmsnzAuthor Commented:
Yup,  I tried to login as a different user and still got that problem.
And all the software have the latest update and same version. I turned off the firewall but still cant fix the problem.
0
 
slamhoundCommented:
Have you got the latest MDAC version and the latest .Net framework? If their MDAC is old the pcs may be comminucating with the 2005 like it's a 2000 SQL server.
0
 
kmsnzAuthor Commented:
Well, I checked all the computers MDAC before, and all have a same version. all used 2.7. I tried ones using 2.8 but looks not working as well.
0
 
kmsnzAuthor Commented:
Thanks all,

I managed to find the answer,It's the bit fields that cause the problem. Specifically how ODBC
handles these with NULL values. Part of what I was doing was adding a bit field into the structure of the table in question and not setting the values to 0 and not setting the default.
So I just set the default value to 0, and run update query.
But still not sure why only some workstations had that problem before and from more than 10.000 records, only a few records have a null ( the rest of the record have a 0 already)
0
 
slamhoundCommented:
That makes sense. A bit field can't have Null in it...It's either True of False. My guess is that Access was finding the Null bit field and a) didn't know what to do with it or b) tried setting it to a valid value.
0
 
modus_operandiCommented:
Closed, 500 points refunded.
modus_operandi
EE Moderator
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now