Solved

how to lock a record in  access using vb6

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now