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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Your far better off with a centralized table approach for handling the locks.
Jim.
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)
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)
I think now its too late...solution is provided...points awarded....although i think Jim's solution can be tweaked to serve its purpose...
<<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.
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.
<<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.
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.
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.
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.
<<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.
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.
UserX has being working on this record from HH:MM:SS:tt