[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to lock a record in  access using vb6

Posted on 2006-07-04
5
Medium Priority
?
421 Views
Last Modified: 2010-05-03

I am working on a vb6 application and access as database

In that i am making the application multiuser.
In that i am updating , inserting , deleting records using sql statments.
now I want that whenever I do updating the  record  then at that time , I want to apply some kind of lock so that other user can not  do anything on that particular record till the user finishes his work.

so Is there is any way to lock a record in access using vb6 (Remember i am sql statments)
0
Comment
Question by:ziorrinfotech
  • 3
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17040180
Hi,
  you have to add 1 or 2 columns to the table, I use LockUser and LockDate
  when the user "selects" the row for editing, the application has to "lock the record first":
   UPDATE yourtable SET LockUser = '<current user>', now() WHERE PK = <primary key value> AND LockUser IS NULL

  after this update, select the row:
    SELECT <columns>, lockUser, LockData FROM YOURTABLE WHERE PK = <primary key value>

  if your now read LockUser = CurrentUser, the user has the "lock" on the row and can later submit the update, which should also release the "lock":

  UPDATE yourtable
    SET <column> = <newvalue>
    , LockUser = NULL
    , LockDate = NULL
  WHERE PK = <primary key value>
    AND LockUser = <current user>

 --> the last condition is to ensure that this user still has the lock (see later)
 --> with the property RecordsAffected you find out if the row was really updated or not.
 
IMPORTANT:
  In case the user abandons the lock (application crash for example), you have to forsee a admin module to free up a lock !!!


0
 

Author Comment

by:ziorrinfotech
ID: 17040224
Hi angelIII
First of all thanks for your help.

Your idea will work good  but the in the case like you mention that if application crashed or user machine reboots sundely then how can i remove locks from those records in which this particular user had applied/.

Do u have any solution for this or is this is any way to check wheather a particular user is connected to access database or not.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17040257
as mentionned, you will need to have a admin module to remove (old) locks.

I have 2 parts:
1 scheduled job that removed locks older that 1 hour automatically
  UPDATE yourtable SET LockUser = NULL, LockDate = NULL where LockDate < DateAdd("h", -1, now())

1 form to unlock locked records.
 filters by
   LockUser
   Primary Key Field
 sort by
   LockDate (desc)
   LockUser

and the interface needs to unlock by setting LockUser and LockDate to null for the selected (locked) records.
0
 

Author Comment

by:ziorrinfotech
ID: 17040321
Yes your solution is also a good idea
but I have one condition if you have solution for this pls give me

condition is
User A open the my application and edit some record but he does not save that record and his machine rebot or my application crash.
Then if user open my applcation then the admin module will remove the locks applied by this user A.

but Suppose this user A does open the application again  then??
and if he does not open the application again then the admin module will not from this user machine and the lock for this user will not be removed

Wht will I do if this condition happened
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 17040538
With "admin module" I mean a form in your application where a "application-admin" can remove the locks MANUALLY.
the "old" locks can/should be removed automatically, but the explicit removal should be done as a manual operation.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question