MS Access SQL Server BE Record Checkout

MS Access SQL Server BE.  I need to create a check out process to prevent multiple users from accessing or updating the same records.  Also, I need a warning message when user attempts to work a record that is already checked out.
deer777Asked:
Who is Participating?

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

x
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.

John TsioumprisSoftware & Systems EngineerCommented:
You will need to put a flag on each row (better an extra timestamp)  and maybe some info like "username" so each user that tries to access the record to get the info that the
UserX has being working on this record from HH:MM:SS:tt
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
If you want more tracking, you might add these 3 fields:

dtmOut, date/time
dtmEdit, date/time
UsrEdit, short text, who has it checked out -- or UsrIDedit if you have a usrs table and can store a Long Integer instead. I normally do this, and keep track of each user with a database property.

I would also add:
dtmAdd, date/time, default value = Now(), date/time a record was created


Code would check dtmOut for no dtmEdit, or dtmEdit < dtmOut -- and could see who has the record. dtmEdit would be filled on the form BeforeUpdate event. This way, the last edits can be tracked too. Is your form designed with an Edit button? Or will editing have to be discerned? Or are you still figuring that out?

Alternately, you could use a related table with a pk that relates to the pk of your data table.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
0

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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and I would not put a flag in the rows.  As the article explains, it sounds great (and works), until you have something happen and your stuck clearing every row of every table.

Your far better off with a centralized table approach for handling the locks.

Jim.
0
ste5anSenior DeveloperCommented:
I disagree with the marked solution. The link Jim has posted is for an Access backend.

SQL Server works differently then Access. Thus using a defensive approach of looking for existing locks is useless to do transactional isolation. It can fail under load, by reporting false locks existing or by right locks not existing.

Here you need a lock table (ideally per entity you want to lock). The lock table is simply the primary key columns of your entity (head) table plus informational columns filled with GETDATE() and SUSER_SNAME(). The primary key columns are here also the primary key. This ensures that only one lock can exist.

A lock is acquired thru an stored procedure which inserts a row into that table.
A lock is released by a stored procedure which deletes your lock.

Cause you need some error handling.

And finally : Why a second post?

SQL Server/Access Front End Need to create a checkout process on primary key (product_number)
0
John TsioumprisSoftware & Systems EngineerCommented:
I think now its too late...solution is provided...points awarded....although i think Jim's solution can be tweaked to serve its purpose...
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I disagree with the marked solution. The link Jim has posted is for an Access backend.>>

 The main point of the article is not the specific implementation, but the overall concept.  The concept can be applied to any data store, Access, SQL, or whatever and it can be used for records, users, processes, or whatever because you have the three types of locks.

 Only requirement with the code as it is shown is that you need to be able to have an exclusive lock on the lock table, which you can certainly do with SQL.

 If you are not able to do that, then you'd need to make it a reservation style process where you write a lock record with a date/time stamp, then go back and check if you are "first in" or not.   If first in, then it's your lock and you can do what you need to do.   If not, then you delete your reservation.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<And finally : Why a second post?>>

 I missed answering this.   Because at the time of the first post, I did not see John's or Crystal's posts, but did after I hit submit on the first post. So I made a second comment.  

 As I point out in the article, leaving lock fields in each table is generally a bad approach.   Granted, the need to go in and clear all the locks in this day and age is becoming few and far between (things don't crash like they used to), but I've seen way too many systems where this is done and when something does happen, then you are stuck with:

1.  Clearing the lock fields in every record in every table.
2. Leaving the locks there and providing some means of clearing them on the fly.   Usually this requires some type of supervisor password to do so.   But either way, this drives users nuts.

 So I disagree with the approach overall.

Jim.
0
ste5anSenior DeveloperCommented:
I have to problems with that article (maybe nitpicking), but:

1) You don't need to test for existence before adding/removing a lock. Cause this may fail due to transactional isolation levels and under load. And of course due to simple concurrency (time passing between test and the lock action).

2) The article misses to explicitly state the we need a physical, unique index. Candidate keys are a logical concept.

btw, when using SQL Server, you may consider using sp_getapplock.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<1) You don't need to test for existence before adding/removing a lock. Cause this may fail due to transactional isolation levels and under load. And of course due to simple concurrency (time passing between test and the lock action).>>

  But you do.    You cannot have multiple lock levels if you do not.  That's why you need to lock a table exclusively while doing the check and placing the lock record.

 If you were sticking to a locking scheme where it was an exclusive lock on a resource, then yes, you would not need to check first, but simply try and write the record.

 But if you do that, then you loose a lot of flexibility in what you can do with the setup.  For example, if I wanted to track users at login so I know who's in the application, then all I would need to do is ask for a "read-only" lock with a ResourceTag  of "USER" and a ResourceSubTag of the actual user.

 If all I had at my disposal was the ability to place exclusive locks, then I could not do that.

<<2) The article misses to explicitly state the we need a physical, unique index. Candidate keys are a logical concept.>>

  um....I could have a long conversation with you on that.  While they are a logical concept, having a physical unique index is not an absolute requirement.   Works better obviously, but it's not an absolute requirement.   But there is one in there in any event (LockID), unless I'm missing your point.

Jim.
0
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
SQL

From novice to tech pro — start learning today.