Query Across Multiple ODBC Connections... "ABAX via AccessMonster.com" <u23056[ at ]uwe> 10.07.2006 19:03:50 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-externaldata/200607/1

Re: Query Across Multiple ODBC Connections... "Amy Blankenship" <Amy_nospam[ at ]magnoliamultimedia.com> 10.07.2006 19:33:12 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 "ABAX via AccessMonster.com" <u23056[ at ]uwe> wrote in message news:630cdc6458362[ at ]uwe... [Quoted Text] > 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. > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-externaldata/200607/1

Re: Query Across Multiple ODBC Connections... "ABAX via AccessMonster.com" <u23056[ at ]uwe> 10.07.2006 19:39:34 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 Blankenship wrote: [Quoted Text] >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 >[quoted text clipped - 6 lines] >> but I am learning. Any suggestions or references would be greatly >> appreciated. Thanks. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-externaldata/200607/1

Re: Query Across Multiple ODBC Connections... "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro[ at ]Spameater.orgZERO_SPAM> 10.07.2006 23:31:43 Hi. [Quoted Text] > so my company has setup each of our clients with their own Oracle db > instance. 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. "ABAX via AccessMonster.com" <u23056[ at ]uwe> wrote in message news:630cdc6458362[ at ]uwe... > 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. > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-externaldata/200607/1