Error 3197- Jet Enginge stopped process

[PurpleUnicorn]

I have a bound form (bound to table 1) with an after_update event that updates table 2 (using a recordset). It is a multi-user application with the data on a server. I sometimes get the 3197 Error - you and another user are attempting to change the same record.... The error occurs during the after_update event mentioned above and the update does not occur - which of course messes up the data and a manual adjustment must be made. It is highly unlikely that more than 1 person is attempting to update a particular record simultaneously every time this error occurs.
As far as I know, the mdbs have the default record locking set for Edited record.

I either need a method of preventing this error from occurring or a method of either preventing or undoing the update on my bound form if the error does occur.

Any insight would be greatly appreciated!

Nancy

 

[tunsarod]

Nancy,

I'm not very familiar with the record locking process. I have built a database which is running over a network but there's so little traffic that I opted for No Locks at all. However if you have Edited records locked the Access help on this reads as follows:

"Edited Records: Microsoft Access locks the record you're editing, so no other user can change it. It might also lock other records that are stored nearby on your disk. If another user tries to edit a record that you've locked, Microsoft Access displays the locked record indicator in the other user's datasheet. This strategy ensures that you can always finish making changes that you start. It is a good choice if you don't have editing conflicts often."

The point here is that Access uses "page locking" which means that when a record is selected for editing by a user a number of records in the file either side of the selected record are also locked. In a table where each record is small in byte size this could mean several dozen or more records. So someone else editing a record in the same table could be restricting your ability to save the changed record.

If the table is set to All Records then the whole table will be locked.

You may have to find a way to force users to release the table so you can grab it and secure it for your own needs. I'm not sure how - perhaps one of our resident wizz-kids can help with a solution???

Rod