Cannot update data in linked table (1 Viewer)

Hi, I am working on migrating an MS Access application's back-end to Oracle. The application itself works fine, in a multi-user environment, when users are working on single records.

My problem begins when I try to perform a multi-record update. For example, running something like this will just crash MS Access -

UPDATE TBL_TO_UPDATE
INNER JOIN sheet1 ON TBL_TO_UPDATE.id = sheet1.id
SET TBL_TO_UPDATE.COMMENTS_clob = sheet1.comments;

The supporting tables are linked to the Access application, using Oracle's ODBC driver.

I am able to perform similar updates - as a test - to any local Access tables. But any updates to linked tables just fails!

I can perform Updates to linked tables, provided I am performing a single record update.

Any ideas?

A further test where I try to update ALL the entries on a single column produces an error, along the lines of "records locked due to violations".

I tried to update 13 records, with the below query (in Access). Access updates the first record and then cannot update the remaining 12 record, because of the lock.

This is the query that I run, for this test:
UPDATE TBL_TO_UPDATE
SET COMMENTS_NEW = 'Test Entry';

Error produced:
Microsoft Access can't update all the records in the update query
Microsoft Access did'nt update ............, 12 record(s) due to lock violations

RE: The supporting tables are linked to the Access application, using Oracle's ODBC driver.
Can you provide more information about how the ODBC driver works?

I attached an image of the method I used to attach to Oracle in a remote geographical area over the company VPN. Is your Oracle server on a LAN?

In the mean time, I remember trying to solve various issues that seemed ... intermittent. One that comes to mind was the ODBC timeout.
Another is the Data Type conversion. I had little control over the Oracle side. They tended to use text for dates and even integers. This required a lot of detail to data type conversions.

While I do have some local Access Tables, the majority of mine are on SQL Server. The Linked Server using the Oracle driver provided stabality and speed (emphasis on speed) over a direct ODBC link.

Do you have a script for creating Linked Tables to Oracle, or are you manually connecting via ODBC?

Hello,

Everything is on a LAN. I have full control over the ORACLE schema, but this is not an Oracle issue. It seems to be an Access/ODBC connection issue.

I have configured the ODBC connection using MS Access' in-built menus. Additionally, using Microsoft's DSN-less script was resulting in the same problem.

As a solution, I implemented PASS-THROUGH. It seems to be working well now.

Thank you.

Would like to know more details on the Pass-Through if you could share them.
On the Oracle ODBC driver, are you using the ones from the basic install or using the SQL Server Native Client 11.0?
I forgot to mention that I use the former. I use this with DSN-Less and have several post on that.
Just wondering if the Native Client might offer some recent enhancement or not?

Microsoft have a KB 303968 - This is how you create pass-through queries.

I used the standard Oracle ODBC driver, as installed on the Windows 7 desktop machines - "Oracle in instantclient_11_2". This is managed by our infrastructure team.