Avatar of Locky123
Locky123 asked on

Pessimistic Locking with Access ADO and SQL Server

Hello All

I am trying to get a definitive answer as to whether it is possible to acheive a pessimistic lock with ADO.
I am using an MS Access 2003 .mdb front end connected to SQL Server 2005.

I have a routine that creates a recordset that I need to be locked from the point of opening (no .edit command in ADO) to the point the update command is issued.

I have opened a connection using the sqloledb provider and set it's cursor locaion to adUseServer (I believe this is the default anyway)

I have then instantiated a recordset and set its 'Lock Type' to pessimistic, 'Cursor Type' to keyset and 'Cursor Location' to server side.

I have tried all manner of combinations of these parameters and I simply cannot achieve a lock.  I can open a new instance of the front end and happily update the record while to other instance supposedly has it locked.

I know that using an sp is probably the way to go, but I don't know enough T-SQL to be confident enough to produce one - and in any case, the options are there, so they should work surely!

I've been at this for three solid days now and it's driving me nuts!

Any help greatly appreciated!

Andy
Microsoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment
Locky123

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
flavo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Locky123

Hi Dave

I have stopped the code in the first instance at the point just before it executes the update statement (i.e. well after it has started the edit) and the second instance still carries on and edits the recordset while the first supposedly has it locked.

This is the code that executes:

I simply can't figure out the problem. :-(
Public Function GetNextCostNumber(strNextNumberName As String, _
                              booIncrementNextNumber As Boolean) As Variant
    
    '******************************************************************************
    'This function will be called from various forms.
    'It carries out the following tasks:-
    '   (a) Picks up the required "Next Number" from the "Next Cost Numbers" table.
    '   (b) Sets the return value to the retrieved "Next Number".
    '   (c) If specified, increments the specified "Next Number"  in the
    '       "Next Cost Numbers" table.
    '******************************************************************************
    
    '=========================
    'Switch on error trapping.
    '=========================
    On Error GoTo ErrHandler
    
    '=============================================================================
    'Set pointer to hourglass to make sure user knows the routine is active if the lock takes a while to establish
    '=============================================================================
'    DoCmd.Hourglass True
 
    '========================================
    'Declare variables for the error handler.
    '========================================
    Dim intLockCount As Integer
    Dim intChoice As Integer
    Dim intRndCount As Integer
    Dim intI As Integer
 
    '=======================================================================
    'Retrieve the specified "Next Number" from the "Next Cost Numbers" table
    'and, if specified, increment the specified "Next Number" in the
    '"Next Cost Numbers" table.
    '=======================================================================
    Dim cnn1 As ADODB.Connection
    Set cnn1 = New ADODB.Connection
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    Dim strSQL As String
    
    cnn1.ConnectionTimeout = 5
    cnn1.ConnectionString = gstrConnect
    cnn1.CursorLocation = adUseServer
    cnn1.Open
    
    cnn1.BeginTrans
    
    strSQL = "SELECT * FROM tblNextCostNumbers " & _
                    "WHERE NextCostNumbersID = 1"
                    
   
    rst.CursorLocation = adUseServer
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockPessimistic
        
    rst.Open strSQL, cnn1, adOpenKeyset, adLockPessimistic
    
    '------------------------------------------------------------------------
    'This will throw a timeout error if the recordset is locked
    '------------------------------------------------------------------------
    rst.MoveFirst
    
    If Not rst.EOF Then
        If rst.RecordCount = 0 Then
            MsgBox "UNEXPECTED ERROR - The application could not find a" & _
                   " 'Next Cost Numbers' record in the 'Next Cost Numbers'" & _
                   " reference table.", _
                   vbOKOnly, "UNEXPECTED ERROR"
            GoTo LeaveSub
    
        Else
    
            'Get the specified "Next Number" and, if requested, increment
            'the specified "Next Number".
            Select Case strNextNumberName
                
                Case "PO"
                    GetNextCostNumber = rst!NextPurchaseOrderNo
                    
                    'If requested, increment the "Next Purchase Order Number".
                    If booIncrementNextNumber Then
                        rst!NextPurchaseOrderNo = rst!NextPurchaseOrderNo + 1
                    End If
                
                Case "HO"
                    GetNextCostNumber = rst!NextHireOrderNo
                    
                    'If requested, increment the "Next Hire Order Number".
                    If booIncrementNextNumber Then
                        rst!NextHireOrderNo = rst!NextHireOrderNo + 1
                    End If
                
                Case "ActualCostReportNo"
                    GetNextCostNumber = rst!NextActualCostReportNo
                    
                    'If requested, increment the "Next Actual Cost Report Number".
                    If booIncrementNextNumber Then
                        rst!NextActualCostReportNo = rst!NextActualCostReportNo + 1
                    End If
                
                Case "ReconciliationReportNo"
                    GetNextCostNumber = rst!NextReconciliationReportNo
                    
                    'If requested, increment the "Next Reconciliation Report Number".
                    If booIncrementNextNumber Then
                        rst!NextReconciliationReportNo = rst!NextReconciliationReportNo + 1
                    End If
                    
                Case "Mentor Recon"
                    GetNextCostNumber = rst!NextMentorReconRptNo
                    
                    'If requested, increment the "Next Reconciliation Report Number".
                    If booIncrementNextNumber Then
                        rst!NextMentorReconRptNo = rst!NextMentorReconRptNo + 1
                    End If
                
                Case "Daily Timesheet Items"
                    GetNextCostNumber = rst!NextId_DailyTimesheetItems
                    
                    'If requested, increment the "Next Id - Daily Timesheet Items".
                    If booIncrementNextNumber Then
                        rst!NextId_DailyTimesheetItems = rst!NextId_DailyTimesheetItems + 1
                    End If
                
                Case "Daily Timesheets"
                    GetNextCostNumber = rst!NextId_DailyTimesheets
                    
                    'If requested, increment the "Next Id - Daily Timesheet Items".
                    If booIncrementNextNumber Then
                        rst!NextId_DailyTimesheets = rst!NextId_DailyTimesheets + 1
                    End If
               
                Case "Financial Statuses Change History"
                    GetNextCostNumber = rst!NextId_FinancialStatusesChangeHistory
                    
                    'If requested, increment the "Next Id - Daily Timesheet Items".
                    If booIncrementNextNumber Then
                        rst!NextId_FinancialStatusesChangeHistory = rst!NextId_FinancialStatusesChangeHistory + 1
                    End If
                 
                Case "HO Items"
                    GetNextCostNumber = rst!NextId_HOItems
                    
                    'If requested, increment the "Next Id - HO Items".
                    If booIncrementNextNumber Then
                        rst!NextId_HOItems = rst!NextId_HOItems + 1
                    End If
                
                Case "Non Job Invoice Items"
                    GetNextCostNumber = rst!NextId_NonJobInvoiceItems
                    
                    'If requested, increment the "Next Id - Non Job Invoice Items".
                    If booIncrementNextNumber Then
                        rst!NextId_NonJobInvoiceItems = rst!NextId_NonJobInvoiceItems + 1
                    End If
               
                Case "Non Job Invoices"
                    GetNextCostNumber = rst!NextId_NonJobInvoices
                    
                    'If requested, increment the "Next Id - Non Job Invoices".
                    If booIncrementNextNumber Then
                        rst!NextId_NonJobInvoices = rst!NextId_NonJobInvoices + 1
                    End If
               
                Case "Payroll Batches"
                    GetNextCostNumber = rst!NextId_PayrollBatches
                    
                    'If requested, increment the "Next Id - Payroll Batches".
                    If booIncrementNextNumber Then
                        rst!NextId_PayrollBatches = rst!NextId_PayrollBatches + 1
                    End If
                
                Case "PO Items"
                    GetNextCostNumber = rst!NextId_POItems
                    
                    'If requested, increment the "Next Id - PO Items".
                    If booIncrementNextNumber Then
                        rst!NextId_POItems = rst!NextId_POItems + 1
                    End If
                
                Case "Standard Credit Note Items"
                    GetNextCostNumber = rst!NextId_StandardCreditNoteItems
                    
                    'If requested, increment the "Next Id - Standard Credit Note Items".
                    If booIncrementNextNumber Then
                        rst!NextId_StandardCreditNoteItems = rst!NextId_StandardCreditNoteItems + 1
                    End If
                
                Case "Standard Credit Notes"
                    GetNextCostNumber = rst!NextId_StandardCreditNotes
                    
                    'If requested, increment the "Next Id - Standard Credit Notes".
                    If booIncrementNextNumber Then
                        rst!NextId_StandardCreditNotes = rst!NextId_StandardCreditNotes + 1
                    End If
                
                Case "Standard Invoice Items"
                    GetNextCostNumber = rst!NextId_StandardInvoiceItems
                    
                    'If requested, increment the "Next Id - Standard Invoice Items".
                    If booIncrementNextNumber Then
                        rst!NextId_StandardInvoiceItems = rst!NextId_StandardInvoiceItems + 1
                    End If
                
                Case "Standard Invoices"
                    GetNextCostNumber = rst!NextId_StandardInvoices
                    
                    'If requested, increment the "Next Id - Standard Invoices".
                    If booIncrementNextNumber Then
                        rst!NextId_StandardInvoices = rst!NextId_StandardInvoices + 1
                    End If
                
                Case "Summary Timesheet Items"
                    GetNextCostNumber = rst!NextId_SummaryTimesheetItems
                    
                    'If requested, increment the "Next Id - Summary Timesheet Items".
                    If booIncrementNextNumber Then
                        rst!NextId_SummaryTimesheetItems = rst!NextId_SummaryTimesheetItems + 1
                    End If
                
                Case "Summary Timesheets"
                    GetNextCostNumber = rst!NextId_SummaryTimesheets
                    
                    'If requested, increment the "Next Id - Summary Timesheets".
                    If booIncrementNextNumber Then
                        rst!NextId_SummaryTimesheets = rst!NextId_SummaryTimesheets + 1
                    End If
                    
                Case "Requisition"
                    GetNextCostNumber = rst!NextId_Requisition
                    
                    'If requested, increment the "Next Id - Summary Timesheets".
                    If booIncrementNextNumber Then
                        rst!NextId_Requisition = rst!NextId_Requisition + 1
                    End If
                    
                Case "Requisition Item"
                    GetNextCostNumber = rst!NextId_RequisitionItem
                    
                    'If requested, increment the "Next Id - Summary Timesheets".
                    If booIncrementNextNumber Then
                        rst!NextId_RequisitionItem = rst!NextId_RequisitionItem + 1
                    End If
                
                Case "Materials Supplied by Wolseley"
                    GetNextCostNumber = rst!NextId_MaterialsSuppliedByWolseley
                    
                    'If requested, increment the "Next Id - Materials Supplied by Wolseley".
                    If booIncrementNextNumber Then
                        rst!NextId_MaterialsSuppliedByWolseley = rst!NextId_MaterialsSuppliedByWolseley + 1
                    End If
                
                Case Else
                    MsgBox "The 'Next Number Name' " & strNextNumberName & _
                           " is not recognised by the 'Get Next Cost Number' function.", _
                           vbOKOnly, "APPLICATION ERROR"
                    GetNextCostNumber = 0
                    GoTo LeaveSub
                    
            End Select
    
            'Update the retrieved "Next Numbers" record (Therby unlocking it).
            rst.Update
            
        End If
    End If
    
    rst.Close
    Set rst = Nothing
''    DoCmd.Hourglass False
 
    cnn1.CommitTrans
    
LeaveSub:
    Exit Function
 
ErrHandler:
    Select Case Err.Number
        
        Case 3197
            '============================================================================
            'Handle Error 3197 - Data in the record has changed since it was last opened.
            'Try to edit the record again.  This automatically refreshes the record to
            'display the most recent data.
            '============================================================================
            Resume
    
        Case -2147467259
            '=========================================
            'Handle Error -2147467259 - The record is locked.
            '=========================================
            intLockCount = intLockCount + 1
            
            'If the lock error has already occurred three times.
            If intLockCount > 3 Then
    
                'Let the user cancel or retry.
                intChoice = MsgBox("The system has been unable to obtain a unique index number for " & _
                                                    strNextNumberName & _
                                                    ". This may be due to another user or process carrying out a large transaction " & _
                                                    "(such as a job download).  Either wait a couple of minutes and re-try or " & _
                                                    "cancel (cancelling will undo the changes you have made to this record. Do you wish to re-try or cancel save?", _
                                                     vbCritical + vbRetryCancel, "RETRY?")
        
                Select Case intChoice
                    Case vbCancel   'The user does not want to try again to obtain the lock.
                        GetNextCostNumber = 0
                        Resume LeaveSub
                    Case vbRetry  'The user wants to try again to obtain the lock.
                        intLockCount = 1
                End Select
        
            End If
    
            'Yield to windows.
            DoEvents
        
            'Delay a short random interval, making it longer each time the lock fails.
            intRndCount = intLockCount ^ 2 * Int(Rnd * 300 + 1000)
            For intI = 1 To intRndCount: Next intI
        
            'Try the edit again.
            Resume
 
        Case Else
            '=================
            'Report the error.
            '=================
            MsgBox "An unexpected error has occurred in the 'GetNextNumber' function.", _
                   vbOKOnly, "ERROR"
            MsgBox "ERROR: " & Err.Number & " " & Err.Description, vbOKOnly, "ERROR"
            GetNextCostNumber = 0
            Resume LeaveSub
    
    End Select
 
End Function

Open in new window

Leigh Purvis

I'm afraid you're quite likely to get me on this here too. ;-)

OK - to address purely this aspect of your requirements (and leave aside all the holistic approach discussion blx) you're not making any obvious mistakes.
Your cursor is Server side - so that's fine.

What code are you using to test this?  Where does it break off to the next instance?  Are you using two distinct MDB's to test - with the same procedure?

If functioning properly you would probably be seeing a failure to even read in the second instance.
The lock on an ADO recordset hitting a SQL Server db should actually see its influence from the recordset being read - let alone Editted (which is actually aligned with what you want).
ASKER
Locky123

Hi Leigh - again!

I was using two distinct mdb files and running it in the intermediate window (to avoid any potential issues with the form's ODBC).  I was breaking the code at the point immediately after it had done the edit on the record (i.e. after this statement)

If booIncrementNextNumber Then
      rst!NextPurchaseOrderNo = rst!NextPurchaseOrderNo + 1
End If

Anyhow - I have actually got it working as it should.  I have simply taken out the statement that was testing for EOF and lo and behold, it works perfectly. Lines 64 to 72 in the snippet)

I have no idea why this has made a difference, but there's no question that doing this has made the ADO locking work exactly as you would expect.

Wrt the actual design issues, the more I think about this, the more inclined I am to lean towards the auto increment option as you suggest.  There are really only about ten tables out of 60ish that I would like to have control over the number that is assigned to it, so I can add an extra column for these and let SQL Server sort the rest out.

I suppose the next question is, can you reccommend a good book on writing stored procedures so I can do these kind of things a bit smarter in the future?

Thanks to you and Dave for the time spent looking at this.

(Leigh - I'll post this to UA also to get closure on the thread)

Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
Leigh Purvis

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Locky123

Ah - The EOF is returning True, but the recordcount was returning -1 on the locked recordset, not 0 as I had supposed it would!  The penny drops!

I've just updated to error trapping to capture any 'missing record' errors that get thrown.

Thanks again for steering me in the right direction.

Andy