Locking records in an Oracle DB

  • Thread starter Thread starter Vince
  • Start date Start date
V

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.
 
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.
Click to expand...

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?
 
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?
Click to expand...

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.
 
Back
Top