Can CR link more than 2 pardox tables.

[ibearian]

I was running CR7 and Paradox 8. But i can't get crystal to link more than two tables. Every time i try i get '0DBC Error:[intersolv][odbc paradox driver][Paradox]Table does not exist' I can link more than 2 in MSQuery & therefore excel but not Crystal ........any ideas? i have tried different tables, different join types, i have even upgraged to 8.5 & still no joy.

 

[kenhamady]

This may be a limitation or problem with the ODBC driver or connection.

It works fine with two tables? I assume you are connecting A-B-C, and you can do A-B and B-C with no error?

Can you view the SQL statement for the report once you add the third table? Can you test that statement within Paradox somehow to see if it is valid? Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[ibearian]

I can get the information in MSQuery (so the sql statemnt is OK) and copy the SQL statement to crystal but still get the same error message.

interestingly i tried to do a very simple MSQuery (with one field from each table) & copied that to Crystal Sql designer. I then opened it as a query data source in Crytal & it worked. But as soon as i made it more complicated (in Crystal sql designer) it brought up the error message.

My main irritation is that windows 3.0 vintage MSQuery can do a job that 2001 Crystal reports can't. Is there any other ODBC driver i can use ? Can i use a borland/paradox native driver (somehow)?

Any ideas?

 

[kenhamady]

I am not sure where to point you on this one,; but I want to confirm that you did the test I was suggesting.

You can't ever move a SQL statement INTO the CR report designer, CR has to generate it's own SQL (the SQL designer is a different animal, and can accept any valid SQL statement). You CAN copy a statement that CR generates OUT to determine if CR is generating valid SQL.

So when CR fails, take the statement from CR and try it in MSQuery. If that exact statement that was generated by CR (and failed) then runs in MS Query, the problem likely would be a CR problem. But, if that statement is invalid when run from either, than the ODBC layer is likely the problem since the SQL is generated by the ODBC layer. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[kenhamady]

BTW, CR has it's own ODBC driver (you are using the intersolve driver) but you need the Intersolve driver if the database is password protected. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[ibearian]

I tried the test as you stated i.e. Copy the sql statement out of CR to MSQuery and it failed. MSQuery said could not open \\server\data\table table (The first table in the sql call). So do you think the CR not compatable with the driver (Intersolv 3.11 32bit paradoxfile (IVIDP13.dll) ?

Thankyou in advance for your help.

I've just seen your new posting. Is the CR driver for paradox Microsofts 'ODBCJT32.dll' ? Beacuse i have tried but i think this only works on erlier versions of paradox, but i will confirm.

Thankyou once again

 

[kenhamady]

Sorry, I don't know the driver name. I just read an old posting on the Seagate web site:

http://support.crystaldecisions.com/kbase/c2000068.asp?

So, the SQL CR is generating is bad.
When MS Query generates it's SQL, is it using the same ODBC connection? Is the SQL different? If the SQL is different on the same connection, then CR must have some type of incompatibility. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[ibearian]

i have the two sql calls, there are a number of differences both formating & in their text. I have tried to connect through DAO, this is fine with one table but with more is incredible slow (hours not minutes to update)

i'm going to try to use a MSAccess as a linked intermeidiary database between paradox & crsytal.

Thankyou in advance for your help

MSQUERY

SELECT Customer.code, Customer.endate, Sltrans.value, Sltrans.accountcode, Invoice.boatname, Invoice.NLCode, Invoice.invalue, Sltrans.invoreceiptno, Invoice.invoiceno
FROM `\\Psserv02\hsm\Live\Data`\Customer.DB Customer, `\\Psserv02\hsm\Live\Data`\Invoice.db Invoice, `\\Psserv02\hsm\Live\Data`\Sltrans.db Sltrans
WHERE Customer.code = Sltrans.accountcode AND Sltrans.invoreceiptno = Invoice.invoiceno AND ((Customer.code>'J0' And Customer.code<'Jzzzzz'))

CRYSTAL REPORTS

SELECT
Customer.`code`, Customer.`endate`,
SLTRANS.`accountcode`, SLTRANS.`invoreceiptno`, SLTRANS.`value`,
INVOICE.`boatname`, INVOICE.`invoiceno`, INVOICE.`invalue`, INVOICE.`NLCode`
FROM
{ oj (`\\psserv02\hsm\live\data`\`Customer` Customer INNER JOIN `\\psserv02\hsm\live\data`\`SLTRANS` SLTRANS ON
Customer.`code` = SLTRANS.`accountcode`)
INNER JOIN `\\psserv02\hsm\live\data`\`INVOICE` INVOICE ON
SLTRANS.`invoreceiptno` = INVOICE.`invoiceno`}
WHERE
Customer.`code` >= 'J0' AND
Customer.`code` <= 'JZZZZ'

 

[kenhamady]

I have seen problems before when CR throws the 'oj' into the FROM clause. This is for outer joins, which would not be compatible with the Inner Joins. CR is generating bad SQL. If you are using the same DSN for both, then the problem is definitely CR.

You might correct it with a patch. I believe that you can download the latest P2SODBC.dll for CR. This is how CR works with ODBC Drivers.

Also, check and see that you are using the latest build of CR, which (I believe) is build 100. The product version would look like 7.0.1.100. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[ibearian]

i'm using CR 8.5 & the p2sodbc.dll is the most recent version. So unfortunately that eliminates your last two points.

I don't suppose you remember where you have seen this problem before & what the cure was?

Is there no way to edit the crystal Sql call?

thankyou again

 

[kenhamady]

Sorry, but your first post says CR7.

You can edit anything except the SELECT expression within CR SQL window. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[ibearian]

I started with CR7 & upgraded to 8.5 dev a couple of days ago.

I have just downloaded the new BDE 5.1.1 (Borland Datbase Engine) & it will allow me to query paradox tables nativly (through the DATABASE>LOG ON SERVER>database file then locating your db, the way you connect to a MSAccess db, i think like ADO). No need for ODBC so is also much quicker. I've checked & i can query three tables.

Thankyou for all your help and although the problem wasn't sorted i have found a better solution.