Union query from 2 different datasource

[aperez54]

Hello

I have a crystal report with data from an Oracle database. I have created a query in MS Access in the exact same structure as the datasource from Oracle and need to do a union query.

How do I do that? I can use the "Add Command" but it throws me an error that it can't find the MS Access table (or vice versa when I add command from the Oracle datasource).

Is it do-able?

Thanks~

 

[aperez54]

I don't think it can be done (I'm using Crystal XI), so I just linked the tables in MS Access and created union query there.
Not my desired method but oh well.

 

[jmd0252]

I don't believe it can be done,, I know you can "union", 2 tables togather from the same database,, but you are bring 2 different databases togather. I have not heard of that being done.

 

[aperez54]

Thanks jmd.

I went through 10 pages of threads here when I searched by "union" and most of them suggested doing the union from a database that has access to both datasources.

Thanks so much!

 

[jmd0252]

ok I was working on a BO project and came across a product on the server,, called business view manger,, it says it allows you to pickup data from multiple sources.. above that I have little or any knowledge. I found it on the crystal enterprise server. Do not know if this helps or not..

 

[Turkbear]

Hi,
You, or your Oracle DBA can create a database link from Oracle to Access (using Oracle's heterogeneous services) and that will allow you to treat the datasources as one by building views of the Access data.

Here are some docs ( from Oracle v9 but concepts still apply)

http://download.oracle.com/docs/cd/B10501_01/server.920/a96544/toc.htm

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[skaestner]

look into something like an OPENQUERY from Oracle to the MS Access ... again this is something like Turkbear suggested.

 

[aperez54]

Thanks everyone!

I wanted to have the flexibility of modifying the query if required. Unfortunately, I will have to go through hoops (regular IT turn around time) if I ask our dba to create the query for me.