a better way to query?

[festdaddy]

I'm trying to learn how to query a DB with VBA in 2007.

What I need to do: grab two pieces of information from a spreadsheet
(location, time), and use this info in a query to return some results.

What I did: I recorded the procedure. Using Ribbon->Data->From Other
Sources->From MS Query, then I manually entered my query paramaters.
This worked fine. I then edited the recorded macro, but this took
awhile to get right. I essentially had to write the whole query
string, instead of just changing a hard value (the one I manually
entered during recording) to a variable.

What I'm hoping someone can help me with is: I'm sure there is a
better way to do this. I've tried searching this group, and the
related results look very different. Could someone point me in the
right direction to understand this part of VBA a little better, or
perhaps just tell me an easier way to get a query done?

Thanks,
-Rob
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub process_data()

endrw = ActiveCell.SpecialCells(xlLastCell).Row
For orw = 3 To endrw
Sheets("output").Select

zc = Cells(orw, 3)
my = Cells(orw, 4)

selectstring = "SELECT dist_by_zip.Zip, dist_by_zip.YB,
dist_by_zip.Percentile, dist_by_zip.RC" _
& Chr(13) & "" & Chr(10) & _
"FROM M360.dbo.dist_by_zip dist_by_zip" _
& Chr(13) & "" & Chr(10) & _
"WHERE (dist_by_zip.Zip=" & Chr(39) & zc & Chr(39) & ") AND
(dist_by_zip.YB=" & Chr(39) & my & Chr(39) & ")"

Sheets("temp").Select

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")).QueryTable
.CommandText = Array(selectstring & Chr(13) & "" & Chr(10) &
"ORDER BY dist_by_zip", ".Percentile")
'original .commandtext line: .CommandText = Array( _
"SELECT dist_by_zip.Zip, dist_by_zip.YB,
dist_by_zip.Percentile, dist_by_zip.RC" & Chr(13) & "" & Chr(10) &
"FROM M360.dbo.dist_by_zip dist_by_zip" & Chr(13) & "" & Chr(10) &
"WHERE (dist_by_zip.Zip='51104') AND (dist_by_zip.YB='1920')" & Chr
(13) & "" & Chr(10) & "ORDER BY dist_by_zip" _
, ".Percentile")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_m360"
.Refresh BackgroundQuery:=False
End With

Next orw

End Sub

 

[joel]

There is a query editor that you can get to two different ways. The 1s
wqay is while you are creating the query in the last menu with th
FINISH b utton. Select the Edit button and press Finish. The 2n
method is selecting a cell in the returned query and then going t
Ribbon->Data->From Other
Sources->.

then in Query editor press the SQL button. You can modify the SQL an
get results instantaneously (including error messages).


The problem I usually find is the FROM and WHERE need to be on a ne
line using VBCRLF (or chr(13) & chr(10)).

This is the way I normally do it. Not much different but a littl
clearer. I add line continuation characters to make it easier t
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")).QueryTable.CommandText
" & _
Array(MySQL

 

[festdaddy]

Thanks Joel. Do yuo happen to know of a source where I could learn a
little more about this? I've been using J. Walk's book (excel 2003
power programming w/VBA), but there isn't much documentation about
accessing SQL with VBA. I've done a couple of searches, but haven't
found much so far. It seems like this end of VBA is not all that well
documented?

Thanks again for your help,
-Rob

 

[festdaddy]

Thank you Joel. I'm just now figuring out that I need to start
learning about ADO. Do you happen to kow of any good VBA related ADO
books or other sources?

Thanks again for your help.
-Rob

 

[joel]

The SQL is just a sdtriong an manipulation of the string format i
pretty simply. there are some good basic examples in the Access VB
help. The VBA language is the same in Access as Excel. I have a boo
that IU bought put it is not much better than the Access VBA help. Th
book has type errors and it doesn't usally work without minor changes.
I get frustrated with the Access code because it usally talks me
couple of hour s to get it working because the SQL is very particular t
the syntax. It is not clear documented that where you need to put th
VBCRLF and where you need to put commas and semicolons and where it i
optional