?
Solved

how to lock a record in  access using vb6

Posted on 2006-07-04
5
Medium Priority
?
413 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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 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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month8 days, 16 hours left to enroll

764 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