p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   VB Databases Basics (http://p2p.wrox.com/forumdisplay.php?f=82)
-   -   ADO Record Locking (http://p2p.wrox.com/showthread.php?t=39533)

pjohanne March 8th, 2006 10:33 AM

ADO Record Locking

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


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.

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.

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. :)

adam_kahtava March 8th, 2006 08:10 PM

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

adam_kahtava March 8th, 2006 08:17 PM

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
        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

pjohanne March 9th, 2006 05:42 AM

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?

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

All times are GMT -4. The time now is 04:05 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.