How to ignore write conflict error message and close form

[Richard-S]

A form used by multiple users can have a write conflict when those users are updating the same record at the same time. This is an auction and record locking is not used intentionally so users cannot hold up another user's action. It is first user who completes the action who updates the record regardless of when the users open the record. If the second action is completed a few seconds later rather than almost at the same time, a messagebox stating another bid was done and a macro closes the form. This works fine.



When there are two almost identical actions done, a write conflict occurs. I added a macro to On Error to explain the user's action was second in a messagebox, and to close the form. That works. But I get another message in between the message box and the form closing, a write conflict, that gives me the options to save, copy or drop. In all events, I want to have the action dropped.

Is there some way to stop the write conflict message from showing and just have the conflict action dropped every time so the form can close without the write conflict message showing? Thanks.

[ItsMe]

Did you split the database and assign each user their own copy of the front end file?

[Micron]

What's not clear here is the order of user actions, and what events are triggering your macros. Regardless, I think you will find that On Error Resume Next will not help, nor will turning off warnings. What might work is to cancel the second update and/or Me.Undo the record edit as I think you'll find it's a message that cannot be turned off. This makes sense since (I think) 99.99% of such occurrences would be important if not downright vital to be aware of. Unless someone else knows how to turn off this message, I think you'll find the answer lies in preventing it altogether. Since appropriate record locking level settings don't seem to be an option for you, either try the aforementioned cancel or undo, or you might want to try using a temp table and attempt to move the record to the main table. Cancel the operation if the key field is already there and delete the temp record. This assumes the db is split and each user has their own FE. If not, you need to relate temp records to each individual user so that you're not wiping out someone else's active record too.

[ItsMe]

...you'll find the answer lies in preventing it altogether...

It is this. I would want to understand why you are getting the message. Understanding why will allow you to address it. Otherwise, it will be like throwing parts at a broken car.

[Richard-S]

It is this. I would want to understand why you are getting the message. Understanding why will allow you to address it. Otherwise, it will be like throwing parts at a broken car.

Thanks for the insight. I was too focused on getting rid of the message rather than resolving the problem. I moved a timer and requery to a different location in the code and it seems to work. I now get the first action saved and the second action getting the message I made for such events.

[Richard-S]

Thanks for your reply. I'm going to see how undo and cancel will work.

[ItsMe]

the Undo method is not for data that has already been committed to the table. Perhaps saving any changes to the data as soon as possible would be beneficial. Something like ...

If me.dirty = true then
me.dirty = false
end if

The first thing I would do is make sure that each User is working from their own copy of the Front End file. Also, if a User is opening multiple forms, try to save and commit changes to the table before the User shifts focus from one form to another.

[Micron]

Unless I misunderstood, what's going on is known; 2 or more users accessing the same record simultaneously with no record level locking by design. When the error is raised, the record hasn't been saved AFAIK. I still think the most practical solution is a temp table, a check prior to attempting to write the record for the second user and a message/cancel routine for that user - given the nature of the business at hand.

[ItsMe]

2 or more users are accessing the same record. However, 1 user can behave like 2 when 1 user opens multiple dynasets of the same fields, like two forms. Also, I have seen Access throw a similar error when two workstations open the same Access file and the same form object. For me, I eliminate these dynamics and then move towards more drastic measures if needed.

I do not know that I would move towards a temp table right away, though. For sure, the nature of the business is going to demand multiple users working with a similar or maybe even identical tuple. I was thinking the next steps might be making a couple or several tables more narrow. For instance, add timestamps to an extremely narrow table that receives many inserts by multiple users. This way, a select query can decide which record(s) wins. Another thing that might be beneficial is using unbound controls and error trapping on a currentdb.Execute method.

[Micron]

I agree with what you say re: other ways to cause write conflicts, and I know even a single user can cause their own write conflict, especially if the issue is incorrect design. However, I've been restricting my comments to the situation at hand; i.e. OP said 2 users accessing the same record simultaneously (because they're both bidding on the same auction item) and the winner of the write battle is the first to complete, not necessarily the first to start. We might be making assumptions as to exactly what is going on. For instance, what does "messagebox stating another bid was done" mean exactly? A record has to exist in order to be "updated" (quoting OP) so perhaps a 'complete' flag is enough and a time stamp is not really necessary since it only seems to matter that someone finished first, not when. Unless I'm missing something, the scenario is:
- there is no record locking on the table in question
- user A starts updating a record (updating is the term used in the original post)
- user B starts updating the same record after user A starts but A has not completed the update
- if A finishes first, that update is to be saved. If B finishes first, that update is to be saved.
- whoever finishes second, their update is not to be saved
- the write conflict that will result from A and B updating the same record at the same time when the second user attempts to save the update is the issue.
This is why I would consider the temp table. If you copy a record to such a table, and the PK of the original record is used as a reference, each user is updating a copy of the original but in the temp. When the action to close the form is undertaken, you check (DLookup or whatever uniquely identifies the original record) to check the flag. If it's not checked, update the record and remove the temp copy for the 'winning' user. If it is checked, message the user (too late bud!), don't run the update and remove the temp copy. No conflicts, no matter how many concurrent bidders of the same item...

So I don't get your idea of more tables (other than the temp I'm advocating) or tuples; which AFAIK, are either ordered sets [mathematics] or a decades old term for a db record. Sorry, I don't follow your meaning or the need for time stamps and more tables.

[ItsMe]

It seems Richard-S is employing logic to avoid write conflicts. However, constraints intrinsic to Access are producing a dialog window we are all familiar with. I do not know of a way to handle this after or when it happens. I only know the steps I take to avoid allowing it to happen. So I have been concentrating on the below quote by Richard-S.

Is there some way to stop the write conflict message from showing and just have the conflict action dropped every time so the form can close without the write conflict message showing? Thanks.

I did not want to stray from the point when I mentioned the split DB and multiple forms. My intention was only to eliminate a couple of possible gotchas, in an effort to stay on point.

The idea behind a narrow table is to allow many sequential inserts while avoiding write conflicts. A query can determine which records trump the others. No need for error trapping, record lock write conflicts, rollback, etc. You just append records to a table and a query determines the results. In the case of a tie between two timestamps, the PK will serve as a tiebreaker.

Although, at this point, I do not believe anyone really cares what you and I think about it. Richard-S has not responded in a while. But yeah, I think I was applying logic as I posted and when I respond to threads on AFN I try to stay on point. Even if I do not communicate my message clearly.

[Richard-S]

ItsMe/Micron: My apologies for not replying. It seems Yahoo started sending these emails to spam and I forgot to check as I thought previous replies did a good job of getting me to a point where the database seems to work very well. I'm going to keep all these suggestions in case something comes up. The reason for not using the usual be/fe is the group and person staging this program is using a regular PC for the program database with multiple wireless connections, both PC, iPad and possibly other iOS devices. They are using remote desktop software to connect. When this combination was tested, iPads would not see the database with linked tables (I have no idea why and gave up) and those PC remotes that did, the system was slow causing conflicts. Since I know nothing of wireless, I've tried to accommodate to the problem using only one database. We are now trying Microsoft's remote desktop that seems to provide better speed and better iOS compatibility, but we don't know yet any possible problems. Although the present system is working well with one or two remote devices, including an iPad, it remains to be seen if a dozen wireless connections will work or overwhelm the one database. Again, thanks for your input and providing suggestions. They are appreciated.

[ItsMe]

A dozen connections is pushing it. However, Microsoft used to advertise 'up to 25 contiguous connections'. However, this is with each PC opening the connection has its own runtime engine. What you are describing is a single PC managing multiple instances of Access.

As for the original issue of the warning, one approach might be to have the form in question open via VBA, using Form_MyFormName vs. MyFormName. Perhaps a custom class module could create the instance of the form. I would probably look to design changes in the tables, also.

I think many of the performance issues will be associated to the fact that many processes will be demanded of the single PC where the Access file is located. Perhaps a 64 bit install of Office/Access would be beneficial here (I would not bank on that though).

[Richard-S]

ItsMe:
I tried splitting the database and the results are positive. It operated fine using Microsoft remote desktop for the PC using Win10-home and better using the iPad. There was better iPad scrolling and it just seemed quicker. I now have a new error message for simultaneous actions that replaced the write conflict, however, the new message allows "end" as well as "debug" to go forward so would be easier for a user to just "end" and go back to the main form. This is where the second user is less than a second later trying to update than the first user. For me, this is a better result.

Thanks for the idea using Access runtime on PCs. At least that would relieve some of the load the iPads or Apple laptops will push to the program PC. We are concerned how this will work with more than the 2-3 devices we are using to test various options and will just have to see what happens.

[ItsMe]

I have to say that it seems you are racing towards a train wreck. The different scenarios using RDP, runtime, etc. will work. However, you will experience interesting things. As for using a Runtime Exception as a way to catch an overwrite, I cannot say that sounds dependable or convenient. Be aware that when there is a runtime exception that all variables will lose their handle and be destroyed. So a string variable that you dimensioned and initialized will become nothing. This may give someone using the full version of Access a false hope that things are normal.