Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005

    Unanswered: PASS THRU QUERY Problem

    I have an Access 2000 database that contains a couple of pass thru queries. These queries access 2 different ORACLE databases.

    Query A reads ORACLE_A database
    Query B reads ORACLE_B database

    Both queries are syntactically correct and can retrieve data. However, I cannot open Query A, close query A and then open Query B. I get the error "ODBC --call failed. [Oracle][ODBC][ORA-00942: table or view does not exit"

    The same is true if I first open up Query B, close query B and then try to run Query A.

    I have to completely exit MS ACCESS all together (not just close the MDB) in order to get Query B to open. But then, I can't get Query A to run.

    It's as if ACCESS doesn't see the odbc properties of each pass thru query. It sees the first set of properties and then retains that and tries to apply it to all other pass thrus. If I read from Oracle B first, all pass thrus that attach to Oracle B work and vice versa with Oracle A.

    I swear this is a new problem! I suspect it may be the result of a new security patch or virus scanner update although that's a guess because I can't think of anything else it could be.

    I'm running Windows2000 SP4. I can duplicate the problem on an XP Box using MSAccess 2003.

    Has anyone experienced this problem and/or know what's going on?

    Thanks in advance.

  2. #2
    Join Date
    Mar 2005
    One other note ...

    You really do not even need to create pass thru queries. The same behavior can be duplicated when LINKING tables to 2 different ORACLE schemas.

    Link a table in ORACLE A
    Link a table in ORACLE B

    Open the ORACLE A table
    I can't open the ORACLE B table that I just linked.

    Exit ACCESS completely.
    Open ORACLE B table
    Can't open ORACLE A table.

  3. #3
    Join Date
    Oct 2003
    Use 2 different ODBC DSNs, one for each Oracle database, and your problem should go away. I do it all of the time.

  4. #4
    Join Date
    Mar 2005

    Thanks - I'm all set now

    Using 2 different DSNs fixed the problem! I guess this was the first time I tried linking to 2 different databases that resided on the same server which allowed me to use the same DSN. Once I created another DSN, I was all set.

    Thanks for the help.

Posting Permissions

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