When encounter "Write Conflict" (Error 7787) in MS access, we get three buttons in the popup:
[Save Record] [Copy To Clipboard], [Drop Changes]
I want to trap this error in Form Error event,
it seems Response = acDataErrContinue in the code is the same as click on [Copy to Clipboard].
How to write a code for the same as selecting the [Drop Changes] botton?
The Response parameter is used by MS Access to determine weather or not to display the default error message. I'm not sure which one is the most convenient but there are three values the Response parameter can have:
Can't you simply trap the error and undo the changes?
This kind of error is a real pain in the neck and one very difficult to manage.
The best solution would be to identify why this error occurs (it should not in an usual situation) and fix the application accordingly. Unfortunately this is not an easy task.
One of the most frequent cause for this error is when Access is used as a front-end and data are stored on a server (SQL Server for instance) and that this server uses triggers or user-defined functions that manupulate those data, like this:
You change a value in Access --> This triggers a manipulation of the same data in the server --> Access considers that "someone else" tried to write the same data in the same time --> this generates a Write conflict error.
One of the most vicious situation I had to face with this error waas like this:
I had an Access Front-end application with the tables and an important part of the business logic on a SQL Server. In one of the tables there was a computed field (for several reasons I could not simply compute the value of that field on demand) and I used a standard T-SQL function to compute the value. Everything worked fine. After a reorganization of the tables in the database I could not use a standard T-SQL function to compute the value and I wrote a user-defined function to produce the same result. From that moment I began to have lots of Write Conflict Errors in the Access front end.
It's only an example, many other situations can cause this kind of problem and unfortunately they are not easy to identify and to solve.
Thank you very much for your reply.
We are using Front-end and Back-end Access, multiuser. If more than one user edit on the same record, this "Write Conflict" message will appear. If you choose "Save Record", you often end up with the database corruption.
We are telling all our users to click [Drop Changes] if they meet this "Write Conflict" message. Now, I want eliminate the popup message by trapping the Error, using "AccDataErrContinue", however, it only do the same as click [ Copy to Clipboard], showing another popup message, like " Do you want save the large contents to your clipboard?" [Yes] or [No].
I hope someone can help me to write the code to select [Drop Changes] button, or if this is impossble, help me to trap the second popup message, choose [No] button for not saving to Clipboard.
Thanks a lot.
I have same error with you, when I checked my code and I found that I forgot to put me.Refresh on the form, this can be caused by more than 1 sub form using the same recordset, but however its my case, who knows its the same case like yours.
The original post is old, but maybe someone with error 7787 will find my post.
I had the error come up with the same symptoms as other users. I'm using an Access 2007 with a SQL Server 2005 back-end. The problem started after I added a few new bit fields to an existing table. The record updates would not commit, the error got trapped, and Access would ask if I wanted to keep the copied data in the clipboard. I never figured out why this happened, but to fix it I first checked the table properties on the SQL Server--didn't find any issues. I then refreshed the ODBC link in the Access linked table manager--no success. Next, I deleted and reestablished the ODBC link for that table in Access and that resolved it. No more issues. Definitely a bugger!
This error is normal when two users are editing the same record.
This just tells you that Access / VBA is not true multi user and it's difficult to trap this particular error and force a desired outcome.
I am surprised that Microsoft hasn't enabled us to deal with this better.
The post above that's having a problem with Front end/back end access is probably using ADO to connect to the back end which is not the best way to interact with Access DB. I prefer DAO when communicating Access to Access.