Update Query - Retry when lock violation

[MarkGallie]

I have an Access Program that runs a number of queries on an Access Database. When running update queries from User Input forms, quite often we receive lock violations and the update does not happen. I have hidden the warnings as these often confuse the users but this can mean that often these missed updates may go unnoticed. Is there a way to detect these warnings and display a message to the user without having to enable all warnings? Ideally I would like to store a record of these failed updates and somehow queue them up to retry at a later time. As a start though just the warning detection should cover what I need. To ensure that these missed updates don't go unnoticed.

[ItsMe]

Error trapping on action queries is difficult. My experience is, either it works or it does not. You can try a loop command using DAO. If you use a Do While or a Do Until EOF you might be able to insert an On Error Exit Do before your Loop command.

You can adjust your error handler to store information in an error log. At the beginning of the procedure you could declare some variables and then have the procedure update the variables as it progresses. The error handler could write the variable values to the table in the event of an error.

I don't know how you update your tables. You might be able to add some code to help avoid write conflicts.

[orange]

Is this a multiuser database? If so, is the database split into front end/back end?

You may wish to investigate Transaction processing --- http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

[MarkGallie]

It's a multi-user database with the data separated from a number of front end pieces. The queries are usually called when entering information into text fields. An example of the queries is:

DoCmd.RunSQL "UPDATE units SET itm_status = ""U01"", itm_substs = 89 WHERE itm_unitrf = """ & OldReg & """ "

In this case, if this particular record was being modified by a different user for a different reason the updates would not happen. I was under the impression that "On Error" functionality only applied to actual errors rather than warnings. What I require is something like "On Warning"

[ItsMe]

.....with the data separated from a number of front end pieces.

What does this mean? And yeah, I suppose you are right about the warning vs. Error thing. I believe that if you are using DAO and try to .Edit to a locked record, it will error. The VBA is trying to lock for editing and if it can't the VBA should error. That link offered by orange might be an approach to avoid the update conflict in the first place.

[orange]

Does each user have their own copy of the front end on their own PC?

[MarkGallie]

I've just had a quick test of the Transaction Processing idea suggested by Orange and it seems to be the way to proceed. Doing it this way classes it as an error rather than a warning so On Error procedures should allow me to get what I'm after. Cheers for the help.