Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2014
    Posts
    7

    Unanswered: Data update not possible

    Greetings!
    I am new here and not very experienced yet, so please bear with me.
    I am running an Access database which I had to split recently. All tables went to the backend, everything else stayed in the front end. The operation was no problem, everything went without a glitch except for one thing.

    I am updating regularly by pulling data from an external oracle table via ODBC and adding it to my own tables. As a mere user, I have no control over Oracle and the server, so I originally set up to transfer 1:1 to avoid any problems.

    This was never a problem and still works as long as the target table is in the front end (where it is not supposed to be now). As soon as I link to the backend table, I get an ORA#1722 error and the operation fails. Except for the link, the tables are exactly the same, and every other linked table works fine. I cannot for the life of me find a difference or an invalid number or other not corresponding formats of any kind. This just doesn't make any sense to me.

    I have tried both an old Access 2003 and a newer 2010 version, no difference.

    I simply have no idea where to look and would be grateful for any help pointing me in the right direction.

    Chris

    Using Win7 Prof, Access 2003/2010

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    How do you import data from the external oracle table (VBA, SQL,...) ?
    Have a nice day!

  3. #3
    Join Date
    Jan 2014
    Posts
    7
    Quote Originally Posted by Sinndho View Post
    How do you import data from the external oracle table (VBA, SQL,...) ?
    I use a rather simple Access query pointing to the linked table.
    I have several others doing the same, but this is the only table accessing external data.

    Chris

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I would try this syntax for the query:
    Code:
    INSERT INTO TargetTable (Field1, Field2, ) IN ExternalDatabase
    SELECT Field1, Field2,      
    FROM SourceTable;
    where ExternalDatabase is the full name to the BE.
    Have a nice day!

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Do your tables in Oracle all have Primary Keys, or at least Unique Indexes, defined for them? Is the PK part of the RecordSource of the Form? If not, I believe the Tables will be Read-Only using ODBC.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Jan 2014
    Posts
    7
    Quote Originally Posted by Missinglinq View Post
    Do your tables in Oracle all have Primary Keys, or at least Unique Indexes, defined for them? Is the PK part of the RecordSource of the Form? If not, I believe the Tables will be Read-Only using ODBC.

    Linq ;0)>
    I do not have access to the actual oracle tables. Mind you, I am but a dumb user and IT just doesn't want to bother with something as trivial as Access. Don't know it, don't want to know it, don't support it! Unfortunately, it is the only db tool that came with my office software and I don't have anything else, so I use what I have.

    I can call the linked oracle tables up in Access and the layout shows serveral fields with the primary key sign. I use a query, no Recordsource here. Atrributes say source:local (no sure if the terminology is correct, I am using a foreign language version), this is the linked table within Access.

    The query contains several of the fields marked with primary keys.

    Chris

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by chrisgorman View Post
    This was never a problem and still works as long as the target table is in the front end (where it is not supposed to be now). As soon as I link to the backend table, I get an ORA#1722 error and the operation fails
    If the process works when updating a table in the FE then the problem does not come from the definition of some table in Oracle.

    Did you try the IN ExternalDatabase syntax I suggested for the query?

    Another solution would consist in either using a temporary table in the FE:
    Code:
    [Oracle]--> [INSERT Query] --> [FE Temp. Table] --> [INSERT Query] --> [BE Table]
    or Open a Recordset on the Query (which should become a SELECT query) then send the data to the BE table (either with a second Recordset or with SQL).

    The first solution should be faster, though.
    Have a nice day!

  8. #8
    Join Date
    Jan 2014
    Posts
    7
    Quote Originally Posted by Sinndho View Post
    If the process works when updating a table in the FE then the problem does not come from the definition of some table in Oracle.

    Did you try the IN ExternalDatabase syntax I suggested for the query?

    Another solution would consist in either using a temporary table in the FE:
    Code:
    [Oracle]--> [INSERT Query] --> [FE Temp. Table] --> [INSERT Query] --> [BE Table]
    or Open a Recordset on the Query (which should become a SELECT query) then send the data to the BE table (either with a second Recordset or with SQL).

    The first solution should be faster, though.
    That is the big question - where does it come from? It doesn't seem to make any sense to work one way and not the other. At least not to me.

    I tried the IN ExternalDatabase syntax you suggested but my reply seems to not have been posted. I get a syntax error when trying to save. Tried the file name with and without extension, no luck.

    The other way would be updating to the FE, then importing the new table to the BE and deleting contents it in the FE. Have I got that right? That's what I do now manually. Quite a pain and not really effective. The more steps I do manually, the more chance of errors creeping in ...

    Chris

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by chrisgorman View Post
    The other way would be updating to the FE, then importing the new table to the BE and deleting contents it in the FE. Have I got that right? That's what I do now manually. Quite a pain and not really effective. The more steps I do manually, the more chance of errors creeping in ...
    Chris
    That can be done automatically using a VBA procedure.
    Have a nice day!

  10. #10
    Join Date
    Jan 2014
    Posts
    7
    Quote Originally Posted by Sinndho View Post
    That can be done automatically using a VBA procedure.
    Sorry for the late reply. It probably can if I would be a programmer which I am not. I do not know how to write code and while I have thought about learning it, my workload just doesn't allow me the time for it. If it would, I am sure my boss would find other things for me to do if I do not have enough work to keep me busy.

    Chris

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It does not need to be very complex. I'll assert that you replace the existing data by "fresh" data from the Oracle server (if you update the existing data it' still doable but a tad more complex logic would need to be used).

    The functional schema would be:
    1. Import data from Oracle in a temporary table in the FE.
    2. Delete data from the destination table in the BE.
    3. Import data from the temporary table (FE) to the destination table (BE).
    4. Delete data from the temporary table.

    Each operation can be performed by a query. Let's name them Query1, Query2, Query3, Query4 (in a production system, I would give them more significant names, though).

    The VBA procedure could just consist in executing the four queries in sequence:
    Code:
    Docmd.RunQuery Query1
    Docmd.RunQuery Query2
    Docmd.RunQuery Query3
    Docmd.RunQuery Query4
    Have a nice day!

  12. #12
    Join Date
    Jan 2014
    Posts
    7
    Quote Originally Posted by Sinndho View Post
    It does not need to be very complex. I'll assert that you replace the existing data by "fresh" data from the Oracle server (if you update the existing data it' still doable but a tad more complex logic would need to be used).

    The functional schema would be:
    1. Import data from Oracle in a temporary table in the FE.
    2. Delete data from the destination table in the BE.
    3. Import data from the temporary table (FE) to the destination table (BE).
    4. Delete data from the temporary table.

    Each operation can be performed by a query. Let's name them Query1, Query2, Query3, Query4 (in a production system, I would give them more significant names, though).

    The VBA procedure could just consist in executing the four queries in sequence:
    Code:
    Docmd.RunQuery Query1
    Docmd.RunQuery Query2
    Docmd.RunQuery Query3
    Docmd.RunQuery Query4
    I guess I am thinking too complicated! This is a workaround I have not thought of and should be doable even without blowing up the database too much. I can add it to the macro as well so the whole procedure should not take all that much longer when updating.

    I will give it a try on Monday and see if it will prevent the hiccup.

    Thanks a lot and enjoy the weekend!

    Chris

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome !
    Have a nice day!

  14. #14
    Join Date
    Jan 2014
    Posts
    7
    Quote Originally Posted by Sinndho View Post
    You're welcome !
    Thanks! It works and my database is finally usable without any manual intervention again.

    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •