Solved

how to lock a record in  access using vb6

Posted on 2006-07-04
5
405 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 142

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 142

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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
add text to end of existing text in file 16 70
How does CurrentUser work? 10 37
MsgBox 4 59
Need Nag Screen for Reboot if system up time is over 14 days 12 47
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 …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

770 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