ODBC to Oracle - Record Locking Issue

[dpimental]

All, I have ms access 2007 and I have a number of user databases connected to back-end access tables and some Oracle tables by odbc.

We are using the 10.02.00.01 version of the Oracle Driver. We are in Windows 7.

Here's the issue we experience. We run an update query in Access, which uses both back-end access tables and odbc oracle tables. The update is refused because of locked records. But, no one is in the records, either in access or in oracle.

In some cases, someone might have been in the same record earlier in the day. But it had been hours since they were in the record. So, it looks like access thinks there is a lock on the record, when there isn't one.

One more fact. If I were to go in to the oracle record directly, I can update the record without any problem.

Has anyone heard of this issue before and know what the solution is?

David Pimental
(US, Oh)

 

[smedvid]

Just a few thoughts...
Off the top of my head - pre-coffee...

1. Can you use a Pass_Through Query to perform the updates on Oracle? More efficient... but can not use if linking to local MS Access Tables.

2. Is your ODBC Registry Entry setup as a "Read-Only" connection? If so, disable.

3. Is your ODBC Registry Entry setup as a "Time Out Enabled" connection? If so, disable.

4. Can you issue a refresh on the Linked Oracle tables before SQL DML?

5. Is something on the Oracle side not releasing the records or refreshing the buffer properly?



htwh,

Steve Medvid
IT Consultant & Web Master

 

[dpimental]

Steve, thanks for the reponse. But updates work for almost all records all the time. There are numberous times that a record or two appear to be locked in access. The apparently locked record belongs to a linked oracle table. But, when I go into oracle I can update it.

Here are some settings that might help.

odbc manager
oracle settings

fetch buffer size = 64000
Enable LOBs is checked
Enable Failover is checked
Retry set to 10
Delat set to 10

Access Settings
Default Record Locking is set to Edited Record
Open Database using Record Level Locking is checked

OLE/DDE Timeout = 30
Refresh Interval = 60
Number of Update Retries = 2
ODBC Refresh Interval = 1500
Update Retry Interval = 250

Let me know if anyone thinks that any of these settings are at issue.


David Pimental
(US, Oh)