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

Question

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

Answer 1

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.

Answer 2

Here's a function that you might consider refactoring to take in a string, and you'll be able to re-use anywhere in your code.

So have a CONST or build a string for your SQL statement, and pop in your SANITIZED, NON SQL INJECTED string as an argument :)

i.e.

strSQL = "SELECT * FROM Customer WHERE ID = " & EnsureParamIsNotSQLInjection(customerID)

... then call the function/sub from anywhere you need to get a recordset/data/execute a statement. Consider creating a handful of data-access functions/subs where you can simply run an UPDATE statement, or retrieve a single value, or retreive a full blown recordset.

The key here is to have these functions all living in one place and reuse them all over the place. Here's a sample in VBScript.

Sub DoStuff(strSQL)

   Set adoCon = Server.CreateObject("ADODB.Connection")


    strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db\Database.mdb") 
    'strConnString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db\Database.mdb")

   adoCon.Open strConnString

   Set rsMain = Server.CreateObject("ADODB.Recordset")

   rsMain.Open strSQL, adoCon

   Do While NOT rsMain.EOF
      customerName = rsMain("CustomerName") 'silly example
      RsMain.MoveNext
   Loop

   rsMain.Close

   Set adoCon = Nothing
End Sub