Multiple pass through queries to same db but different schema Problem

[MikeAuz1979]

Hi,

[Originally posted in Access queries but reposted here in case there's a VBA solution]

Using access 97 and connecting to Oracle I have 2 pass through queries that are connecting to the same oracle db but two different schemas in that db.

Access to these different schemas is by the login so I have different logins in either pt query which works fine when you run the 1st query but when you run the second it gives the error 'Table not found', this happens regardless of which order you run the queries and resets itself if you close and open the access db. (I.e open 1st, close db,re-open db open 2nd is all fine.)

Here's the connection strings I'm using:
ODBC;DRIVER={ORACLE ODBC DRIVER};SERVER=PRMB;UID=QLDPip;PWD=QLDPip;DBQ=PRMB;

ODBC;DRIVER={ORACLE ODBC DRIVER};SERVER=PRMB;UID=IKOW;PWD=IKOW;DBQ=PRMB;

My initial guess is that access remembers the login details for a particular server the first time u connect and then re-uses those details for the next query rather than actually using the details in the 2nd query, hence why the table doesn't exist as it's still looking in the first schema.

Anyone come over this before? any idea how to get access to use the connection details in each query rather than remembering?

Thanks for any Help
Mike