Locking records in an Oracle DB

[Vince]

Hi all,
I have an Access (2003) frontend db using Oracle backend (10gR2). I
wish to lock records such that if another user tries to edit a record
already "opened" it would give them a message indicating try again
later. I have written the followng vba to acheive this without luck in
actually locking the record. The syntax on the passthrough query is
correct, but when I get the "locked" dialog and check the db, I find
not a single record has been locked.


Private Sub Form_Current()

Dim qdf As DAO.QueryDef
Dim rstLock As DAO.Recordset

Set qdf = CurrentDb.CreateQueryDef("")

qdf.Connect = getConnString
'ODBC;SERVER=<server>;DSN=<dsn>;UID=<uid>;PWD=<pwd>;
qdf.SQL = "select * from dirt.lock_test where id = " & Me.ID & "
for update nowait"

Set rstLock = qdf.OpenRecordset

MsgBox "locked"

End Sub

The form is based on the table dirt.lock_test (named dirt_lock_test)
in my Access db.

Does anybody have success in locking an Oracle record to prevent
another user from making changes to it? Thanks.

 

[Vince]

Hi all,
I have an Access (2003) frontend db using Oracle backend (10gR2). I
wish to lock records such that if another user tries to edit a record
already "opened" it would give them a message indicating try again
later. I have written the followng vba to acheive this without luck in
actually locking the record. The syntax on the passthrough query is
correct, but when I get the "locked" dialog and check the db, I find
not a single record has been locked.

Private Sub Form_Current()

Dim qdf As DAO.QueryDef
Dim rstLock As DAO.Recordset

Set qdf = CurrentDb.CreateQueryDef("")

qdf.Connect = getConnString
'ODBC;SERVER=<server>;DSN=<dsn>;UID=<uid>;PWD=<pwd>;
qdf.SQL = "select * from dirt.lock_test where id = " & Me.ID & "
for update nowait"

Set rstLock = qdf.OpenRecordset

MsgBox "locked"

End Sub

The form is based on the table dirt.lock_test (named dirt_lock_test)
in my Access db.

Does anybody have success in locking an Oracle record to prevent
another user from making changes to it? Thanks.

Could it be that the event fires and issues a commit? In which case
the lock would not hold. How can one leave a transaction open?

 

[Bob Hairgrove]

Could it be that the event fires and issues a commit? In which case
the lock would not hold. How can one leave a transaction open?

You need to turn auto-commit off in the ODBC properties dialog. Also, check the
documentation for "transaction isolation" in the ODBC docs -- I believe you want
"serializable". This needs to be set in the same ODBC setup dialog for your
Oracle connection. By default, Access (and Oracle, I believe) use "read
committed" ... but it has been awhile since I worked with Oracle and ODBC.

If all else fails, you can do an ODBC trace to see whether these options are
actually kicking in.