I'm using sql string to delete some data from tables. When someone locking rows then Ms Access generates error. So is there some code which could I use before running delete query, to check whether someone is currently blocking records which I want to delete?
Check whether someone is currently blocking records (1 Viewer)
- Thread starter radek225
- Start date
- Local time
- Today, 05:43
- Joined
- Feb 28, 2001
- Messages
- 27,275
To my knowledge, you cannot tell ahead of time that a particular record or table is locked unless you yourself program a way to know that. You might do better by putting a trap on the event and, if it fails, delay for a second and try again.
You can use a DAO database object to support a DAODB.Execute method for SQL, for which you can use a dbFailOnError option that would cause a failed query to roll back and trigger a trap at the same time. Your trap handler could then detect particular error cases by number (sorry, can't tell you which ones to expect here) and if you get one that is indicative of a lock collision, you can choose to resume execution at a "Retry" point rather than a "Give up and go away" point.
You can use a DAO database object to support a DAODB.Execute method for SQL, for which you can use a dbFailOnError option that would cause a failed query to roll back and trigger a trap at the same time. Your trap handler could then detect particular error cases by number (sorry, can't tell you which ones to expect here) and if you get one that is indicative of a lock collision, you can choose to resume execution at a "Retry" point rather than a "Give up and go away" point.
- Local time
- Today, 11:43
- Joined
- Sep 12, 2006
- Messages
- 15,679
so the question is - why would you want to delete records that someone is using?
- Local time
- Today, 05:43
- Joined
- Feb 28, 2001
- Messages
- 27,275
Dave, good point.
Radek - If the record is meaningful to user A, why would you allow user B to delete it? This is a DESIGN question, never mind what is implemented. Please understand, it does not matter that you have a good reason. The point is, you need to KNOW why you allow it as opposed to accidentally running into the question because you had not anticipated such an event. If it was not your intent, then the problem isn't that you had a collision of usage but that you allowed that type of usage collision in the first place.
Radek - If the record is meaningful to user A, why would you allow user B to delete it? This is a DESIGN question, never mind what is implemented. Please understand, it does not matter that you have a good reason. The point is, you need to KNOW why you allow it as opposed to accidentally running into the question because you had not anticipated such an event. If it was not your intent, then the problem isn't that you had a collision of usage but that you allowed that type of usage collision in the first place.
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 18:43
- Joined
- May 7, 2009
- Messages
- 19,246
dangerous, could cost you trouble.
delete when nobody is using your database.
on multi-user environment, a user never sees that the record he is working on is already deleted until he performs a Requery. an error will occur if he tried to save the record that is already deleted.
delete when nobody is using your database.
on multi-user environment, a user never sees that the record he is working on is already deleted until he performs a Requery. an error will occur if he tried to save the record that is already deleted.
- Local time
- Today, 06:43
- Joined
- Jan 23, 2006
- Messages
- 15,393
radek225,
I agree with the others regarding the deletion of records that are being used by others.
Sounds like a little more analysis of the related business process is required to make absolutely sure that what you intend to do should be done. Many orgs do not delete records physically -they set a boolean (eg IsDeletedYN sort of thing) to logically delete a record. But t he record remains physically for audit purposes etc. Others may move a record to archive or something similar. But to delete records that are or maybe used by others sends up a red flag.
I agree with the others regarding the deletion of records that are being used by others.
Sounds like a little more analysis of the related business process is required to make absolutely sure that what you intend to do should be done. Many orgs do not delete records physically -they set a boolean (eg IsDeletedYN sort of thing) to logically delete a record. But t he record remains physically for audit purposes etc. Others may move a record to archive or something similar. But to delete records that are or maybe used by others sends up a red flag.
