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

 

[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