SQL VBA Excel

[Nanja]

Hi All,

I have some problems with the following code. It is used to import data from a database into excel. The code combines two parts with the UNION command. The two parts itself work correctly on their own, but I cannot combine the results of both SELECT Statements.

The code gives currently only the results of the part after the UNION command.


sSql = "SELECT invent.unit_id as ROL, invent.claim_num as KLACHT, claim_item.reject_id as AFKEUR, invent.ts_wound as TIJD"
sSql = sSql & Chr(13) & "" & Chr(10)
sSql = sSql & "FROM {oj Prod.dbo.invent invent LEFT OUTER JOIN Prod.dbo.claim_item claim_item ON invent.claim_num = claim_item.claim_num}"
sSql = sSql & Chr(13) & "" & Chr(10)
sSql = sSql & "WHERE (invent.claim_num = 603)"
sSql = sSql & Chr(13) & "" & Chr(10)

sSql = sSql & "UNION" & Chr(13) & "" & Chr(10)

sSql = "SELECT invent_hist.unit_id as ROL, invent_hist.claim_num as KLACHT, claim_item.reject_id as AFKEUR, invent_hist.ts_wound as TIJD"
sSql = sSql & Chr(13) & "" & Chr(10)
sSql = sSql & "FROM {oj Prod.dbo.invent_hist invent_hist LEFT OUTER JOIN Prod.dbo.claim_item claim_item ON invent_hist.claim_num = claim_item.claim_num}"
sSql = sSql & Chr(13) & "" & Chr(10)
sSql = sSql & "WHERE (invent_hist.claim_num <> 0) And (invent_hist.claim_num = 1007)"
sSql = sSql & Chr(13) & "" & Chr(10)


Does anyone know what is wrong with the code? I know the database itself dos accept UNION, but I am not sure this can be used by excel this way.

any help is appreciated/
Nanja