There is a query editor that you can get to two different ways. The 1st
wqay is while you are creating the query in the last menu with the
FINISH b utton. Select the Edit button and press Finish. The 2nd
method is selecting a cell in the returned query and then going to
Ribbon-Data-From Other
Sources-.
then in Query editor press the SQL button. You can modify the SQL and
get results instantaneously (including error messages).
The problem I usually find is the FROM and WHERE need to be on a new
line using VBCRLF (or chr(13) & chr(10)).
This is the way I normally do it. Not much different but a little
clearer. I add line continuation characters to make it easier to
understand.
SelectSQL = "SELECT dist_by_zip.Zip, " & _
"dist_by_zip.YB," & _
"dist_by_zip.Percentile, " & _
"dist_by_zip.RC" _
FromSQL = "FROM M360.dbo.dist_by_zip dist_by_zip"
WhereSQL = "WHERE (dist_by_zip.Zip=""" & zc & """) AND" & _
"(dist_by_zip.YB=""" & my & """)"
OrderSQL = "ORDER BY dist_by_zip, .Percentile"
MySQL = SelectSQL & vbcrlf & FromSQL & vbcrlf & OrderSql
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DRIVER=SQL Server;SERVER=COMPUTERNAME;UID=MYIDNUM;" & _
"APP=2007 Microsoft Office system;WSID=COMPUTERNAME;" & _
"DATABASE=M360;Trusted_Connection=Ye"), _
Array("s")), _
Destination:=Sheets("temp").Range("$a$1")).QueryTa ble.CommandText =
" & _
Array(MySQL)
--
joel
------------------------------------------------------------------------
joel's Profile:
http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=150822
Microsoft Office Help