Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default msQuery: is JOIN command possible in Excel?

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?


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4,391
Default msQuery: is JOIN command possible in Excel?

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?




  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default msQuery: is JOIN command possible in Excel?

Thanks for your answer.

Do you think in VBA is the same?

TIA,

"NickHK" wrote:

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?





  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4,391
Default msQuery: is JOIN command possible in Excel?

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...e+Search&meta=

NickHK

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

Do you think in VBA is the same?

TIA,

"NickHK" wrote:

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?







  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default msQuery: is JOIN command possible in Excel?

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" wrote:

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...e+Search&meta=

NickHK

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

Do you think in VBA is the same?

TIA,

"NickHK" wrote:

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?










  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4,391
Default msQuery: is JOIN command possible in Excel?

So are you using ADODB or a QueryTable/MSQuery ?

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

NickHK

"sharon" wrote in message
...
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" wrote:

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...e+Search&meta=

NickHK

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

Do you think in VBA is the same?

TIA,

"NickHK" wrote:

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?










  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default msQuery: is JOIN command possible in Excel?

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?






  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default msQuery: is JOIN command possible in Excel?

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?







  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default msQuery: is JOIN command possible in Excel?

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?









  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default msQuery: is JOIN command possible in Excel?

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?












Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Join tables like inner join in Access ryanp Excel Discussion (Misc queries) 2 July 18th 08 03:35 PM
msquery in Excel 2007 Philippe Cand Excel Discussion (Misc queries) 1 June 22nd 06 03:06 AM
Msquery and join titi Excel Discussion (Misc queries) 2 April 17th 06 02:19 PM
excel 2000, vba and msquery(sql) pmz Excel Programming 1 October 29th 03 02:23 PM


All times are GMT +1. The time now is 02:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"