ODBC cannot access Oracle tables under different schemas in one session

I'm an user, not IS guy, and i'm not sure whether i'm on topic. I have an Access / Oracle problem, where Access people couldn't help.

Until recently, several branches of our department either had their own 8.1.7 Oracle server, or they had separated instances (SIDs) on a bigger host. The oracle database structure, table names etc, is the same in every branch.

In my workstation (W2000, Access 97) i had multiple ODBC names, each referring to the proper server / SID of every branch. Within MS Access 97, I linked via ODBC tables from every server, and by pushing a command button, I could run a series of queries interrogating the same table of every branch, one after another, and could consolidate their data into a local table for subsequent analysis.

Recently, the IS people set up a single department host running Oracle 9, where every branch has their own tables under separated schemas. They all refer to the same server and database name, then according to the id/pw with which they log on the server, they see their own tables.

From Access, i again can see every branch's tables, by linking the server and giving the proper id/pw when promped for. Hower, i am no longer able of running a series of queries, for after i have taken the data from the first table, when i try to query the second table under another schema, ODBC reports an Oracle error that "the table name does not exist". However, if i exit Access, reenter, and manually run the query, the table is seen and the query completes successfully.

In other words, i cannot access tables under different schemas from a single session of Access / ODBC. I must exit and reenter Access in order to shift to another schema and query their tables, which is most unpractical.

Why does this happen and how can I run my queries under a single Access session?

Thank you for any insight you could provide.

VA Received on Sat Dec 03 2005 - 11:48:06 CST