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_Con nection=Ye"), Array
("s")), Destination:=Sheets("temp").Range("$a$1")).QueryTa ble
.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

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

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

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

The SQL is just a sdtriong an manipulation of the string format is
pretty simply. there are some good basic examples in the Access VBA
help. The VBA language is the same in Access as Excel. I have a book
that IU bought put it is not much better than the Access VBA help. The
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 a
couple of hour s to get it working because the SQL is very particular to
the syntax. It is not clear documented that where you need to put the
VBCRLF and where you need to put commas and semicolons and where it is
optional.


--
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