I have a question regarding what to when a user may have a record open and another user is trying to edit it. Is there any way to knock off the first user on a split database?
Or how do I address this issue?
I just find that there are updates being dropped occasionally and this may very likely be the reason why.
Why are people updating a record at the same time someone else is updating it? This is actually a very rare occurrence in most applications and perhaps you need some procedural changes to avoid it.
Access gives a very confusing, three option error message when this conflict occurs. I'm pretty sure you can trap the error in the On Error event but I don't have code handy. I'm not at my computer. You might also be able to check in the Form's BeforeUpdate event as the last line of code, to see if the record is locked by someone else but computers are quick so that won't necessarily solve the problem if between the check and the actual save someone manages to bet in there with an update.
The usual way to do this is involves adding a field to any record for which this problem occurs. Your users probably have some ID or code or SOMETHING that you can tell them apart. If you have something such as a user ID number, this is perfect. Make the new field the same data type as this number. Then you need two queries. Let's call the table in question tX. Call the new field LockerID. The specific record will also need an ID, call it RecID. The user's ID will come from login information or other information, however you decide who is using the front end. The record ID that you want also has to be pre-determined from perhaps a search, but it doesn't really matter how you find it.
I'm going to define this as though the right user ID is in a variable called TheUserID and the desired record is TheRecID.
The only other wrinkle is to perhaps put a time limit on this or otherwise put safeguards so that if you have a locked record that has been locked for more than X, you clear out the LockerID. That would require adding a DATE field showing the last time the record was locked or unlocked.
NOTE that there is a safety catch built into the update. If the record is already owned, the LockerID will NOT be 0 so the original update will not lock it. The safety catch at the end is similar; you can't unlock the record you didn't lock.
I asked WHY the conflict was happening because the only time it is expected is if you are all working from the same task list. In that case, you need to be proactive as Doc suggested because you KNOW there will be conflicts. If you are not working from the same task list, then the problem should be rare enough to not be a problem.
It is a very rare occurrence but if I would implement DocMan's approach - which doesn't seem to be too much extra work it would reinforce the DB's accuracy and predictability. Would it work to add generic code in each of the relevant form's BeforeUpdate event?
When I use the technique suggested by Doc, I include a date and time as well as the userID. In nearly 50 years of development work, I've needed to do this only three times and in all cases it was because they users were working from a single task list and so needed to know that someone had already started working on the task. When I updated a record tying it to a specific user, I also refreshed the task list form although the task list on the other PCs didn't get refreshed until the users did something. The task stayed on the list until the user who took it marked the task complete but the task list showed the assignment.
You didn't answer the "WHY". There is no need to go to these lengths UNLESS you actually have a collision problem that needs to be solved. Just because you like the idea and you think it makes the app somehow safer isn't a good reason to do it.
The structure of this database is a main job list. Users can update the jobs e.g. mark as complete by opening the main job form or by knocking off the summary form which lists jobs which are due today. So if a job is knocked off the list and marked as complete, it may be opened at the time by another user which results in a collision problem.
Almost sounds like you are implementing a Help Desk work list. If so, take a VERY close look at how much labor costs would go into this thing and search the web for Help Desk software. You might be surprised how many packages are out there. If that is where you are going, just remember that you get what you pay for. But by buying commercial off-the-shelf (COTS) products, you get two extra things: (1) It is likely to be up and running more rapidly and (2) if you pick a reputable vendor, you have product support availability.
Actually our system has been up and running for several years already but we are constantly improving based on user feedback and changes in requirements. This was just one issue that has arose from use.
Good to know though.
The problem with setting in-use flags, is that it introduces some other issues.
1. If a user's app crashes, the flag might be wrongly left permanently set to "in use", so an admin needs a way to clear the lock flag
2. If a user locks a record, and then goes to lunch etc, other users may be locked out.
3. More importantly, anyone using general locking strategies needs to consider any consequent unlocking strategy.
If the lock is part of a number of processes, some of which have already succeeded before the lock failure, you may need to unwind the previous changes as well as back out of the current lock. Dealing with concurrency issues is often very complex. It might be needed in high end financial systems, where it is critical that all parts of a high value transaction succeed, but is often overkill for less critical processes. It also needs possible complicated processing to recover from any failure, hence the default "optimistic locking" strategy is generally the best to use
One of the things that makes this type of process easier to manage is adding a TimeStamp (I think there is a new name for this type of field) to each table. This field is automatically changed by the database engine each time the record is updated. Therefore you always know if the record you are looking at is the record that was saved and no update squeeked in between.
