• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 66
  • Last Modified:

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.
0
deer777
Asked:
deer777
  • 5
  • 2
  • 2
  • +1
1 Solution
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now