How do I get the data from an SQL query in microsoft Access VBA?

ms-access, sql, vba

Solution

It's a bit dated, so you might want to grab a book on the subject. But, here's a ton of access resources and some tutorials and examples as well. But, basically ...

Dim dbs As Database
Dim rs As Recordset
Dim strSQL As String
Set dbs = CurrentDb

strSQL = 'your query here

Set rs = dbs.OpenRecordset(strSQL)

If Not (rs.EOF And rs.BOF) Then
  rs.MoveFirst
  'get results using rs.Fields()
Else

'Use results

Per comment: Take a look at the recordset class. It contains a collection called Fields that are the columns that are returned from your query. Without knowing your schema, it's hard to say, but something like ...

rs.MoveFirst
Do While Not rs.EOF
   'do something like rs("SomeFieldName") 
   rs.MoveNext
Loop

Like I said, your best bet is to grab a book on this subject, they have tons of examples.

Problem

Hey I just sort of learned how to put my SQL statements into VBA (or atleast write them out), but I have no idea how to get the data returned? I have a couple forms (chart forms) based on queries that i run pretty regular parameters against, just altering timeframe (like top 10 sales for the month kinda of thing). Then I have procedures that automatically transport the chart object into a powerpoint presentation. So I have all these queries pre-built (like 63), and the chart forms to match (uh, yeah....63...i know this is bad), and then all these things set up on "open/close" events triggering the next (its like my very best attempt at being a hack....or dominos; whichever you prefer). So I was trying to learn how to use SQL statements in VBA, so that eventually I can do all this in there (I may still need to keep all those chart forms but I don't know because I obviously lack understanding). So aside from the question that I asked at the top, can anyone offer advice? thanks

Original source