Visual Studio 2 users on same record


I'm using visual studio with access, is there a way to prevent 2 users from editing the same record? Like if user 1 opens a record while user 2 already has it open, is there a way that user 1 gets a warning message that he can't edit the record?

Thank you for the help.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The only way Access can handle that is to use a "flag" system. Essentially, you do this:

1. Add a column to every table, named something like "bLocked"
2. When a user begins to edit the record, set the value of bLocked = True
3. When a user tries to edit a record, check that value. If bLocked = True, then inform the second user the record is locked.

This has quite a few pitfalls, of course. If User1 opens a record and then goes to lunch, then a 3 hour meeting, you have a record that's locked for half a day. Also, if there you have an "abrupt end" to your program (or the database connection), you could be left with records with bLocked=True where no one is editing the records.
Most applications have so little risk of the same record being updated at the same time by multiple users that people just don't deal with it.  They allow the database engine to handle the issue.  Access just gives the second user a message with three options.  Granted they are confusing, but you can trap the error and give the user some more understandable option.  Of course, I have no idea what you are getting when you use VS to create a form so perhaps you can tell us what happens.  Access actually marks the record with a circle with a slash through it if you are showing the record selector so the user does sometimes have a visual clue if he is paying attention.  The locking is controlled by the database engine so Access wouldn't complain until the  second user tried to save the record.  In my case, since the data I was attempting to control was actually a "set" of data related to a single client, any record in the set could be locked or none might be locked if the user finished with one update and moved on to the next so it was especially important to control the task as a set.

The one (and ONLY one) application that I ever had to deal with potential conflicts was one that had a task list feature.  Users generally just toot the top task and started working one it.  Therefore I needed a way to mark the task as "in process" so they would skip it but not prevent them from opening the record if they really wanted to view it.  I used a LockedDate similar to what Scott suggested above.  I won't bore you with details unless you want to know exactly how I handled it.  As Scott intimated, there is more to it than simply marking the records as locked.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Since you're using Visual Studio, do you have a command button coded to save the record?  If so the records are not likely to be edited/saved at the same time... but rather one user might make and save changes in the background the time another user is viewing the data.   Another way to handle this is to have a date/time column to indicate a record's last modification.  This date/time can be read when the form is initially populated, and then read again at the beginning of the command button's code (immediately prior to the save).  If the timestamp when the button is clicked is more recent than the timestamp when the form was populated, then you can inform the user that another user/process has edited the data, giving them options such as loading the new data or overriding it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.