msQuery: is JOIN command possible in Excel?

[Guest]

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?

 

[NickHK]

AFAIK MSQuery does not support a UNION.

NickHK

 

[Guest]

Thanks for your answer.

Do you think in VBA is the same?

TIA,

 

[NickHK]

There is no direct connection between VBA and SQL, but...

You can use components to access databases and depending on the
drivers/providers used, UNION and other SQL features may or may not be
available.
I'm not familiar with DBF files, so I can't say, but you start by looking at
ADO (or possibly DAO).
Maybe one/some of these will help
http://www.google.co.uk/search?hl=en&q=excel+ado&btnG=Google+Search&meta=

NickHK

 

[Guest]

Hi again,

I don't think is a matter of DBF tables... once you have done a connection
between Excel and a table ( I use ADODB) my problem is how to read from two
(or more)tables and then put this query into the excel, ALL THIS MUST BE DONE
IN JUST ONE SQL sentence. This is the point... just in one sql sentence...(in
Excel)

Cheers,

 

[NickHK]

So are you using ADODB or a QueryTable/MSQuery ?

MSQuery does not support UNION (IIRC), but connecting to DBF with ADO
should.

NickHK

 

[Guest]

I was trying both. My goal is get a VBA SQL sentence like

sSql="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%'"

So, read same fields in differents tables to get one query, and then put
that in a Excel sheet.

Any clue?

TIA

 

[MH]

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

 

[Guest]

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]

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

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.

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

 

[Guest]

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

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

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.

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

AFAIK MSQuery does not support a UNION.

NickHK

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?

 

[NickHK]

You can test your SQL against your data in the DB (Access, MySQL, dBase,
whatever) that you are querying, to see what results you should get.

But as your code does not appear to populate a RecordSet, nothing is
returned to Excel.
Check the help for Recordset and possibly CopyFromRecordSet.

NickHK

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

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

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.



:

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

AFAIK MSQuery does not support a UNION.

NickHK

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?

 

[MH]

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

MH

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

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

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.



:

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

AFAIK MSQuery does not support a UNION.

NickHK

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?

 

[Guest]

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.

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

MH

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

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

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.



:

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

AFAIK MSQuery does not support a UNION.

NickHK

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?

 

[MH]

'Try this:

Sub ReadOpe()

Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sSQL As String

cnn.Open "driver={Microsoft dBase Driver (*.dbf)};" & _
"driverid=277;dbq=c:\VB\"

sSQL = "SELECT IPROD,VDATE,PRICE,UNITS FROM LIN06.dbf " & _
"WHERE IDPROD Like '50404%' " & _
"UNION ALL SELECT IPROD,VDATE,PRICE,UNITS " & _
"FROM LIN07.dbf WHERE IDPROD Like '50404%'"

rs.Open sSQL, cnn, , , adCmdText

Application.ActiveCell.CopyFromRecordset rs

rs.Close
Set rs = Nothing

cnn.Close
Set cnn = Nothing

End Sub

'If this does not work, post back with any error messages etc.

'MH

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.

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

MH

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


:

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

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.



:

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

AFAIK MSQuery does not support a UNION.

NickHK

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?

 

[Guest]

This is what it was looking for!

Great. Thanks a lot.

'Try this:

Sub ReadOpe()

Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sSQL As String

cnn.Open "driver={Microsoft dBase Driver (*.dbf)};" & _
"driverid=277;dbq=c:\VB\"

sSQL = "SELECT IPROD,VDATE,PRICE,UNITS FROM LIN06.dbf " & _
"WHERE IDPROD Like '50404%' " & _
"UNION ALL SELECT IPROD,VDATE,PRICE,UNITS " & _
"FROM LIN07.dbf WHERE IDPROD Like '50404%'"

rs.Open sSQL, cnn, , , adCmdText

Application.ActiveCell.CopyFromRecordset rs

rs.Close
Set rs = Nothing

cnn.Close
Set cnn = Nothing

End Sub

'If this does not work, post back with any error messages etc.

'MH

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.

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

MH

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


:

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

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.



:

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

AFAIK MSQuery does not support a UNION.

NickHK

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?