Linking tables thru multiple databases

[netusepk]

Hi,
I am trying to link tables thru two different databases(Visual Fox Pro), using crystal reports 8.0, but whenever I try doing it thru DSN's it doesnt seems to recognize the link or relation and doesn't give the required output. Then I tried using ADO's then its giving me a cartesian product for the output, rightnow I am trying to use OLE DB setup, which is relating properly but is very slow as it takes a long time to retrieve the records.
Can anyone suggest me a better way to access the database records.

Thanks

Jack

 

[kenhamady]

With the DSN example, you log into both, add a table from both, and then link the tables in the linking expert?

What is the error that you get? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[netusepk]

I am not getting any errors as such, when I generate the report it doesnt seem to recognize the linked tables, just displays data only from one of the DSNs(database), the values from the other database prints out as blanks.
That is the reason I tried with ADO's(produced cartesian product for the result) and now with OLE DB(performance issue, takes too long to generate).
Thanks
Jack

 

[kenhamady]

Did you link the two tables (Database, Visual Linking?) Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[netusepk]

Ken,
I did link the tables in Visual Linking, but for some reason it doesnt seems to recognize the links, do you think some thing I am missing some where?
Jack

 

[kenhamady]

I can't think of why.

Go to the database menu and use the SHOW SQL QUERY option. Paste the SQL statement here.

Can you list the contents of each table separately? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[netusepk]

Ken,
Just now I tried to look at the Show SQL script but for some reason whenever I add more that 2 tables from 2 DSN's it don't seems to work(greyed out).
I tried adding tables from one single DSN it seems to work, wont work with multiple tables from both the DSN's.
Also in the Visual Linking, link option button(Grayed out) for the tables from different DSN.
Do you know why this is happening?
Thanks

Jack

 

[netusepk]

Adding to the adove comment, I got this message when I tried adding tables for second DSN as
"Database error message:
More than one datasource or a stored procedure has been used in the report. Please make sure that no SQL Expression is added and no Server Side group by is performed."
Jack

 

[kenhamady]

When you log into 2 data sources, there are limitations. You can't do outer joins or SQL expressions becuase you are forcing most of the work to be done within CR. That is probably why you can't see the SQL.

However I would expect a basic link to work, albeit slowly.

Are you sure that you can retrieve records from both tables (one from each DSN) if you use them by themselves? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[netusepk]

No I cant retrieve with both the dsn's, Yeah if I use just one dsn and linkup tables then it works fine, problem arises whenever I try to pulling tables from another dsn, Now that I know that it limits on linking option.
Is there another way to link tables out side Crw? What do you think is the best way to approach such a problem?
Thanks for the assistance.
Jack

 

[kenhamady]

You missed my question. I know you can't do both at the same time. I was asking if you have tried both tables by themselves. This makes sure that the problem isn't in the connection to the table. Try one DSN by itself and then try the other by itself. Confirm that you can read both before combining them.

I have done reports that link 2 different types of data (Like Dbase and Access) and this works for me.

But, since you are in Access you could link one table to the other MDB as an external table and then run the report to that MDB. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[netusepk]

Ken,
Sorry for the confusion, I did try with each of the dsn's and tables within that(dsn) and it seems to work properly. I am using Visual Foxpro d/b not Access.
Thanks
Jack

 

[kenhamady]

This may be a VFP problem, maybe the ODBC Driver doesn't support this. It works in other platforms. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[netusepk]

What's VFP ? Is there any other method to tackle this problem?
Thanks
Jack

 

[MalcolmW]

Create a view in VFP that combines the two tables - this would put the linking issue at the database level, where it would be faster and more reliable. I presume you know what VFP is, and just had a momentary lapse ;-) Malcolm

 

[crystallizer]

I have two tables in an Oracle Database. Table A contains a field X and table B contains only two fields, Y and Z.

Table A also contains the fields U, V, and W. There is no foreign key constraints in either of the tables, but B.Y and A.X refer to the same object. In essense I want to write a report with the query

SELECT DISTINCT(A.X), B.Z, A.U, A.V From A, B WHERE A.X=B.Y.

Crystal Reports does not let me link tables A and B manually. (Apparently manual linking is allowed only for ODBC databases.) It also does not let me use the query given above.

Any solutions?

Thanks you very much.

 

[kenhamady]

Could you post this as a new question, instead of as a follow-up to an old question. Also include how you are connecting any error messages you receive. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Guest_imported]

I was having the same problem with a report linking tables from two different databases (Access and Sybase). I went to the Seagate website and their reccomendation is to use a subreport. Apparently Crystal does not allow linking two tables from different databases in one report (if you think about it, how do you build a query to send to both DSNs at the same time). With a subreport you can access one database in the main report and the other in the subreport and then link the two in subreport links.