Access update query of linked Oracle tables not updating properly

Question

I have a linked Oracle table to an Access database which has two columns I am trying to update:

Oracle: EAUSER_WELL_SUMMARY
- UWI (PK)
- MN_FORM
- MN_SRC

I am using an Access table in the database to update the two columns in the linked Oracle table:

Access: TMP_FORM_SRC
- UWI (PK)
- MAIN_FORM
- MAIN_SRC

Here is the SQL that is being used:

    UPDATE eauser_well_summary 
       INNER JOIN [tmp_form_src] 
               ON eauser_well_summary.WELL_UWI = 
                  [tmp_form_src].WELL_UWI 
    SET eauser_well_summary.MAIN_HZ_FM = 
              [tmp_form_src] ! [MAIN_FORM], 
       eauser_well_summary.MAIN_HZ_SOURCE = 
       [tmp_form_src] ! [MAIN_FORM_SRC];

When I run this update query, which has around 3,300 records, the MN_FORM column remains un-updated, but the MN_SRC column ends up with a merged jumble of sources. For instance, the sources are GS, GGX, TEAMDB, TRANSFORM_OVERRIDE, and TRANSFORM. What ends up in the column is GGXBORM_OVERRIDE, GGXGXBORM_OVERRIDE, TRANSFORMNSFORMIDE, etc.

Now, if I run the update query for just one well, it updates both columns correctly. It is only when I try to do more than one well that the problem appears.

I tried updating just the MN_FORM, but I get the following error message:

"Microsoft Access didn't update 0 field(s) due to a type conversion failure, 0 record(s) due to a key violation, 3361 record(s) due to lock violations, and 0 record(s) due to validation rule violations. Do you want to continue running this type of action query anyway?"

When I clicked Yes, one record did update.

I can't figure out the lock violations because I can update the records individually, and also, this is a test Oracle database with only me in it, so no one else is working in it. This is the same with the Access database.

I've searched all over the web, but cannot find anything that can help me with this issue. Any help or direction would be useful.

Thanks!

Update: Based on the article SunKing0 provided along with some additional direction I found from Erik von Asmuth (thank you both), I've pretty much discovered that the problem is on the Oracle side. If I create an Access table of the linked Oracle table and run the update query, it runs with no lock violations. Erik suggested using a DLOOKUP to get around the lock violations, but that didn't work.

Anyone have any suggestions on how to get around the lock violations when updating an Oracle table through Access? Funnily enough, it only happens in batch mode--if I update just one record using the update query, it updates with no errors or issues.

MS Access UPDATE with INNER JOIN: stackoverflow.com/questions/12882212/… — SunKnight0

Heather Heather · Accepted Answer · 2018-04-02T12:55:38

Well, after trying everything under the sun, one of my coworkers found a document online about a known error in the Oracle ODBC driver. It recommended using the Oracle driver provided by Microsoft.

I ran the ODBC Administrator by going to C:\Windows\SysWOW64\odbcad32.exe, then selected Microsoft Oracle ODBC to create my connection. I then re-linked my Oracle table using the new connection and was able to update the table with no issues using the following code:

UPDATE EAUSER_NFX_HZ_WELL_SUMMARY 
INNER JOIN TMP_FORM 
ON EAUSER_NFX_HZ_WELL_SUMMARY.WELL_UWI = TMP_FORM.WELL_UWI 
SET EAUSER_NFX_HZ_WELL_SUMMARY.MAIN_HZ_FM = [TMP_FORM]![MAIN_FORM], 
 EAUSER_NFX_HZ_WELL_SUMMARY.MAIN_HZ_SOURCE = [TMP_FORM]![MAIN_FORM_SRC];

Here is the link to the article talking about the bug:

Error updating linked table

Access update query of linked Oracle tables not updating properly

1 Answers