×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Can CR link more than 2 pardox tables.

Can CR link more than 2 pardox tables.

Can CR link more than 2 pardox tables.

(OP)
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.

RE: Can CR link more than 2 pardox tables.

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.

RE: Can CR link more than 2 pardox tables.

(OP)
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?

RE: Can CR link more than 2 pardox tables.

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.

RE: Can CR link more than 2 pardox tables.

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.

RE: Can CR link more than 2 pardox tables.

(OP)
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

RE: Can CR link more than 2 pardox tables.

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.

RE: Can CR link more than 2 pardox tables.

(OP)
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'


RE: Can CR link more than 2 pardox tables.

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.

RE: Can CR link more than 2 pardox tables.

(OP)
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

RE: Can CR link more than 2 pardox tables.

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.

RE: Can CR link more than 2 pardox tables.

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close