[SOLVED] Paradox DB SQL Multiple JOINS

Paradox DB SQL Multiple JOINS

I'm working on a legacy VB6 project and I need to make a JOIN call like this:

SELECT C.Cnum, C.RealDate, M.Name, R.Price, R.Qnt, R.RealPrice, R.QntP, R.QntR, M.Name
FROM "CHECK" C 
LEFT JOIN "RCHECK" R ON C.Cnum = R.Cnum 
LEFT JOIN "PCHECK" P ON C.Cnum = P.Cnum 
LEFT JOIN "MONEY" M ON P.Curency = M.Sifr 
LEFT JOIN "MENU" MN ON R.Sifr = MN.Sifr 
WHERE C.Cnum > 0 ORDER BY C.Cnum

I use "Driver={Microsoft Paradox Driver (*.db )};DriverID=538" as a part of connection string but it seems it doesn't support more than one join! Which is weird.

Any ideas how to solve/workaround it?

And yes, when I run this query in Borland Database Desktop, it works fine.

Update 1:

My VB Code:

Dim Conn As New ADODB.Connection
Dim sConnStr As String
Dim sQuery As String

sConnStr = "Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Paradox 5.X;CollatingSequence=ASCII;DBQ=C:\DBTOFTP\BUFF;DefaultDir=C:\DBTOFTP\BUFF;PWD=SOMEPASS;"

sQuery = "SELECT C.Cnum, C.RealDate, M.Name, R.Price, R.Qnt, R.RealPrice, R.QntP, R.QntR, M.Name " & _
    "FROM ""CHECK"" C " & _
    "LEFT JOIN ""RCHECK"" R ON C.Cnum = R.Cnum " & _
    "LEFT JOIN ""PCHECK"" P ON C.Cnum = P.Cnum " & _
    "LEFT JOIN ""MONEY"" M ON P.Curency = M.Sifr " & _
    "LEFT JOIN ""MENU"" MN ON R.Sifr = MN.Sifr " & _
    "WHERE C.Cnum > 0 " & _
    "ORDER BY C.Cnum"

Conn.ConnectionString = sConnStr
Conn.Open

Solution

Some old drivers often requires that multiple JOINs must be enclosed in parentheses.

Try something like this:

FROM 
    (
        "CHECK" C
        INNER JOIN 
        "RCHECK" R
            ON C.Cnum = R.Cnum 
    )
    INNER JOIN 
    "PCHECK" P
        ON P.Cnum = C.Cnum