Wow,

It's already working, via MSQquery (which unbelievably works...) but I will
feel more comfortable via VBA and filling a Recordset (which I had supposed
it was filled automatically..)

Thanks for your patience.



"MH" wrote:

Try using the query I gave you in MSQuery, it works for me.

MH

"sharon" wrote in message
...
Thanks for your answer.

I don't really know if it works fine. At least id doesn't give any error.
I can't find out how to populate range with cnn.Execute sSQL because I
see
no results.
I thought it should show the sql result...
What I'm missing?

TIA


"MH" wrote:

I think that the SQL you should be using is:

SELECT IPROD,VDATE,PRICE,UNITS
FROM LIN06
WHERE IDPROD Like '50404%'
UNION ALL SELECT IPROD,VDATE,PRICE,UNITS
FROM LIN07
WHERE IDPROD Like '50404%'

This assumes that you want to select records from both tables where the
field IDPROD (possibly a typo, is this the same field as IPROD?) begins
with
"50404".

Hopefully this will give you the rows you require.

MH

"sharon" wrote in message
...
I was reconsidering my question and I think I was wrong with the use of
UNION.
I 'm trying to read records from two identical tables LIN06 and
LIN07(idprod,vdate,price,units) where idprod like '50404%', and put the
result rows say in activesheet().range("a4")

I wrote this new code, but it doesn't work since it gives a syntax
error
in
FROM clause:

Sub ReadOpe()

'Dim sTab6 As String, sTab7 As String
Dim sSQL As String
Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=c:\VB\"
.Properties("Extended Properties") = "dBASE 5.0;"
.Open
End With

'sTab6 = "[LIN06]"
'sTab7 = "[LIN07]"

sSQL = "SELECT IPROD,VDATE,PRICE,UNITS FROM (SELECT * FROM LIN06,
SELECT *
FROM LIN07) WHERE IDPROD LIKE '50404%'"
cnn.Execute sSQL

cnn.Close

End Sub

Is this enough clear?

Thanks for your help.



"MH" wrote:

Just tested this in Excel 2003 and MS Query does support UNION
queries.

I think it's the syntax of the SQL itself, if Sharon could post her
table
structure and what she is trying to achieve then I might be able to
help.

MH

"NickHK" wrote in message
...
AFAIK MSQuery does not support a UNION.

NickHK

"sharon" wrote in message
...
I am writting a SQL sentence in msQuery and it says "Impossible to
add
(SELECT table", in the next sentence:

SELECT id_User,id_PC,vTime FROM (SELECT 1 AS PC_new.id_User
`c:\PC_office`\PC_new.DBF PC_new UNION ALL SELECT 2 AS
PC_old.id_User
`c:\PC_office`\PC_old.DBF PC_old)
WHERE id_User Like 'TED%'

Is it possible to do in Excel? What's wrong in my sentence?
Any idea?