Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
msQuery: is JOIN command possible in Excel?
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 "NickHK" wrote: 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
msQuery: is JOIN command possible in Excel?
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 "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? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
msQuery: is JOIN command possible in Excel?
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? |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
msQuery: is JOIN command possible in Excel?
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? |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
msQuery: is JOIN command possible in Excel?
'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 "sharon" wrote in message ... 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? |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
msQuery: is JOIN command possible in Excel?
This is what it was looking for!
Great. Thanks a lot. "MH" wrote: '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 "sharon" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Join tables like inner join in Access | Excel Discussion (Misc queries) | |||
msquery in Excel 2007 | Excel Discussion (Misc queries) | |||
Msquery and join | Excel Discussion (Misc queries) | |||
excel 2000, vba and msquery(sql) | Excel Programming |