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
Solved

how to lock a record in  access using vb6

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Macro which automatically sends attachment to Outlook 14 78
message box in access 4 51
Excel VBA, find a string in a column, update a cell 7 86
Hide vba in gp 7 104
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

837 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