Pessimistic Locking with linked table into Oracle

[neil_turner]

Hi All,

Yet another question. Im trying to lock a row of data in a linked table into an Oracle database. At present im trying to use Pessimistic Locking. I use a function to open a recordset for the row that I want to lock, I then test to see if the rocord is already locked if not I carry on. This all appears to work fine but when I test it with a duplicate database there is no lock. When I check on oracle it shows up no locks for my oracle user ID. Ihave tested my code against an Access database and tried the same procedure and it locks fine. Is this just a restriction of the access odbc connection (DAO?).

At the moment this application is in development and I'm wondering if its a case of making the application Access FE/BE with the backend still connected to the oracle database. Then seeing if the lock call works.

Another workaround I can think of is to connect for the lock using ADO/RDO connection. However if someone can see anything obviously wrong with what im trying to do then please shout! Or any better suggestions?

Thanks
Neil

my locking code is :

Public Function AttemptLock(strQuery As String, _
rsResult As Recordset) As Boolean
On Error GoTo e_trap

' Called From : All code that requires a record locked for update
' Overview : Attempts to open a recordset, this checks for locks and assigns
' a lock if the recordset can be opened.
' No error checking as this is what we use to tell if there is a lock

Dim intLockCount As Integer
Dim intRndCount As Integer
Dim intRefreshCount As Integer
Dim intLoop As Integer

Set rsResult = CurrentDb.OpenRecordset(strQuery, dbOpenDynaset)
If Not (rsResult.EOF And rsResult.BOF) Then
rsResult.LockEdits = True
rsResult.Edit
rsResult.Fields(0) = rsResult.Fields(0)
End If
AttemptLock = True

Exit Function
e_trap:

Select Case Err
Case 3197
' Data in the recordset has changed since it was opened.
' Try to edit the record again. This automatically refreshes
' the recordset to display the most recent data.
intRefreshCount = intRefreshCount + 1
If intRefreshCount < 5 Then Resume
Case 3260 ' The record is locked.
intLockCount = intLockCount + 1
' Tried to get the lock twice already.
If intLockCount > 2 Then ' Let the user cancel or retry.
AttemptLock = False
Else
' Yield to Windows.
DoEvents
' Delay a short random interval, making it longer each time the
' lock fails.
intRndCount = intLockCount ^ 2 * Int(Rnd * 300 + 200)
For intLoop = 1 To intRndCount: Next intLoop
Resume ' Try the edit again.
End If
Case Else
ErrorLog Err, Err.Description, "AttemptLock"
End Select

End Function

Public Function CloseLock(strForm As String)
On Error GoTo e_trap

' Called From : All forms that require a lock to be maintained
' Overview : checks the form to see if a recordset is still open.

Dim blnRS As Boolean
Dim rsLocked As Recordset

Select Case strForm
Case "frmActivityAdd"
Set rsLocked = rsActivity
End Select
blnRS = True
rsLocked.MoveFirst
If blnRS Then rsLocked.Close

Exit Function
e_trap:

Select Case Err
Case 91
blnRS = False
Resume Next
Case Else
ErrorLog Err, Err.Description, "CloseLock"
End Select

End Function

 

[Pat Hartman]

Locking is handled by the RDBMS. So Jet handles Access table locking and Oracle handles locking of any ODBC linked tables. I don't believe that the lock settings in your Access db have any impact on the Oracle db.

At the moment this application is in development and I'm wondering if its a case of making the application Access FE/BE with the backend still connected to the oracle database. Then seeing if the lock call works.

The fe cannot link through the be to Oracle or anywhere else. Links are ALWAYS directly to the database where the table physically resides.

Will there be so much contention with this app that you really need to worry about record locks. I think you're getting a little wrapped up in this locking business. You are better off spending your time handling updates that fail rather than trying to prevent the failures.