Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB Databases Basics
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
VB Databases Basics Beginning-level VB coding questions specific to using VB with databases. Issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB Databases Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 8th, 2006, 10:33 AM
Registered User
 
Join Date: Mar 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default ADO Record Locking

Hi,

I was hoping I could get some help with the scenario below:

Scenario:

This is based on a Call logging system created in MSAccess 2000.

We have User1 and User 2. There is a table called Call and a table called Company.

Call - Stores a new call in the system.
Company - Stores a Call_Ref Counter ID against each company.


When a new Call is added the Call_Ref ID is incremented by 1.

The Users can create a new Call in a form called "Add New Call". The Form is linked to the Call Table.

User1: Goes into "Add New Call" Screen. Selects a company from the drop down box. A Call_Ref Field is updated (reads the value of the Counter against the Company name in table Company) Rest of the details are filled in.

User2:
Goes into "Add New Call" Screen. At the same time as User1. Selects the same company from the drop down box. A Call_Ref Field is updated (reads the value of the Counter against the Company name in table Company) Rest of the details are filled in.


As User1 has not yet saved the Company Counter has not yet incremented. So the Call_Ref ID's are the same for User1 and User2.

User1 Saves - Call Created with Call_Ref i.e 100 in table Call. Company Counter Incremented by 1.

User2 Saves - Call Created with the same Call_Ref as User1 (100) in table Call. Company Counter Incremented by 1.

This creates Duplicate Call_Ref ID's against a company in table Call. The Call_Ref ID for a company needs to be unique.

I want to lock the Record in the Company table that matches the selected Company from the drop down box in “Add New Call” Screen.


i.e
User1 chooses company Microsoft from the drop down box.
Looks up Call_Ref counter value in table Company against Microsoft
Locks Record
Populates Call_Ref field in “Add New Call Screen”

User2 comes in. Selects Microsoft.
Message displays saying record locked please wait.

User1 Saves.
Call_Ref ID incremented in table Company.
Unlock Record

User2 tries to select Microsoft again.
No problem as record is now unlocked.


Is there a way I can do this with DAO or ADO?

Code examples would be greatly appreciated. As I’m new to VB and unsure on syntax. :)
Reply With Quote
  #2 (permalink)  
Old March 8th, 2006, 08:10 PM
Wrox Technical Editor
 
Join Date: Dec 2005
Location: , , Canada.
Posts: 271
Thanks: 0
Thanked 0 Times in 0 Posts
Default

In short you want to lock a particular record in an Access Database.

Look into using "pessimistic locking".

Implementing Record-Level Locking for an Access Database with VB: http://www.vbcity.com/forums/topic.asp?tid=15914

- A.Kahtava
Reply With Quote
  #3 (permalink)  
Old March 8th, 2006, 08:17 PM
Wrox Technical Editor
 
Join Date: Dec 2005
Location: , , Canada.
Posts: 271
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here's part of an example using Pessimistic locking at the Record-Level.

Private Sub CmdModify_Click()
    'Set the Query String
    SQL = "Select * from Individual where IndividualID=24"

    'Re-set the Recordset variable
    Set RsModIndividual = New ADODB.Recordset

    'Open the Query into the Recordset
    RsModIndividual.Open SQL, dbrms, adOpenKeyset, adLockPessimistic, adCmdText

    Dim RecordDate As String 'Variable to store a Field data.

    'Here we are transferring one Field value named LastUpdated into a string variable
    RecordDate = RsModIndividual![LastUpdated] 'Transferring Last Updated Field value

    If Check_Record_Status(RecordDate) Then 'Call the Record lock function
        Load FrmModIndividual 'Load the Modification Form
    Else
        MsgBox "Try Editing after a while ", vbInformation, "Record Locked by User"
        Exit Sub
    End If

End Sub

Code taken from: http://www.vbcity.com/forums/topic.asp?tid=15914

- A.Kahtava
Reply With Quote
  #4 (permalink)  
Old March 9th, 2006, 05:42 AM
Registered User
 
Join Date: Mar 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your response adam :)

I can implement pessimistic record locking no problem. The issue is even though the record is locked, it still has read access. I need to deny any other user read access to the record when a User has it selected.

Is there a way to do this in ado?

i.e
User1 Locks Record
Deny Read Access to any other User
User1 Selects different Company name
Record Unlocked


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
ADO vs DAO pessimistic locking knowledge76 Access VBA 3 December 3rd, 2008 12:17 PM
Record locking Stanny Access 1 February 11th, 2006 12:57 PM
Record locking - user needs the next queued record cbtoolkit SQL Server 2000 0 December 6th, 2004 08:29 AM
ADO Locking Skittle Pro VB Databases 9 November 14th, 2003 11:54 AM
Locking a record Bune SQL Server 2000 3 August 26th, 2003 09:50 AM



All times are GMT -4. The time now is 03:46 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.