none
ODBC - update on a linked table <table> failed. (Error 3157) RRS feed

  • Question

  • I am using Access 2013 with a linked table from an Oracle 11g database. I am executing a very simple update statement:

    UPDATE USP_INTERCO_DETAIL_TEST SET USP_INTERCO_DETAIL_TEST.STATUS = "Y";

    The status column is a varchar2.

    The update returns either ORA-01461 or ORA-01722 but when I click on the help it goes to a page for the ODBC error 3157.

    I have verified that the database is read/write and so is the table. There are no locks on the table. The table has a foreign key assigned in Access. It can be read across the network so the database is available. There are no rules on the table. I linked the table as the table owner so there should not be any permission problems.

    To connect to the database, I am using the Oracle instant client 11_2.

    What can cause this error and how can I troubleshoot the root cause?

    Thanks!

    Nancy

    Tuesday, May 2, 2017 7:13 PM

All replies

  • What happens if you open the linked table in datasheet view? Can you edit a field? If not, you might have to delete the linked table and link to it again. The table MUST have a primary key.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, May 2, 2017 7:22 PM
  • Bill,

    Yes, when I linked the table, I gave it a unique key. In testing, I found something very odd. the Table has 12847 rows in it. If I try to update one column with "Y" then it returns the error messages mentioned previously. If I narrow the update down so it is only updating 287 row, then the update puts a "Y" in that one column for one row but all the rest of the rows are updated to null.

    this update statement cause different error message. This morning it is returning ORA-07122 Invalid number when I am updating a varchar2(1) column:

    UPDATE USP_INTERCO_DETAIL_TEST SET USP_INTERCO_DETAIL_TEST.STATUS = "Y";

    And this update statement updates one row with "Y" and the rest of the 287 rows with null:

    UPDATE USP_INTERCO_DETAIL_TEST SET USP_INTERCO_DETAIL_TEST.STATUS = "Y"
    WHERE USP_INTERCO_DETAIL_TEST.JE_HEADER_ID = 58601149;

    We are using the Oracle Instance Client 11_2. What would you recommend?

    Thank you for your help!

    Nancy

    Wednesday, May 3, 2017 2:21 PM
  • Hello Nancy,

    According to ODBC – update on a linked table <table> failed. (Error 3157)

    Possible causes: The update would have caused a rule violation.

    >>The update returns either ORA-01461 or ORA-01722

    I think the update causes rule violations. 

    I suggest you post on Oracle forum to get professional support to fix these rule violations.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, May 4, 2017 6:37 AM
    Moderator
  • Sounds like there is a rather serious issue with your Oracle Client installation. Most of those errors make no sense based upon your description.

    Also, you have to be careful about using the Microsoft OLEDB/ODBC Drivers for Oracle since they do not support Oracle data types such as CLOB and BLOB. You could try switching to Oracle's ODBC driver, if you are not currently using it, to see if anything changes.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 4, 2017 12:50 PM
  • The other idea would be to switch to a passthrough query, which has the server process the update statement, bypassing any ODBC "helpfulness".

    -Tom. Microsoft Access MVP

    Thursday, May 4, 2017 1:38 PM