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?
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.