Query Across Multiple ODBC Connections...

  • Thread starter Thread starter ABAX via AccessMonster.com
  • Start date Start date
A

ABAX via AccessMonster.com

Okay - so my company has setup each of our clients with their own Oracle db
instance. If I was wanting to do some cross instance reporting, what would
be the best method? Fortunately, each of client utilizes the same table
structure. So one query calling the individual table names should work. I
believe that VBA will be the way to go - I have limited experience with it
but I am learning. Any suggestions or references would be greatly
appreciated. Thanks.
 
I'd say the best solution is to combine all the databases into one instance,
and shoot the DBA who decided to have difference DB instances for each
client.

HTH;

Amy
 
Unfortunately - murder is not an option. :) What would be the best
solution to combine all the databases into one instance? Each DB instance
has over 300 tables so view creation may be a little sticky as well. Thanks
for your thoughts.

Amy said:
I'd say the best solution is to combine all the databases into one instance,
and shoot the DBA who decided to have difference DB instances for each
client.

HTH;

Amy
Okay - so my company has setup each of our clients with their own Oracle
db
Click to expand...
[quoted text clipped - 6 lines]
but I am learning. Any suggestions or references would be greatly
appreciated. Thanks.
Click to expand...
Click to expand...
 
Hi.
so my company has setup each of our clients with their own Oracle db
instance.
Click to expand...

Whoa! Your company, or your clients, are paying good money to Oracle for
each one of those instances. The accepted way to do it is to assign each
client his own schema within one Oracle instance. That way, the tables,
data, permissions, et cetera, are all separate. Have your Oracle DBA create
the separate schemas with tables and other objects, assign synonyms, and
grant permissions. Then create views of the relevant tables in the multiple
schemas for each of the reports you need, grant your user SELECT permissions
on these views, then link to them from Access as if they were tables. That
way, all the work is done by the database engine and you just display the
results in Access reports.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
Back
Top