MS Access VBA/SQL question! Pretty Simple

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

Although convoluted, your way is easier than trying to build the SQL statements in VBA. Remember, saving the queries individually allows you to manipulate them visually. Also, there are some performance benefits.

A better understanding of SQL would help you consolidate and simplify your existing queries (if they even need to be simplified. It sounds like they are doing a lot of work, so you may need all 64 queries anyway).

That said, it's pretty easy to execute SQL queries in code:

Dim strSQL as String
strSQL = "UPDATE Table MyTable SET fieldname = 1 WHERE fieldname = 0;"
DoCmd.RunSQL strSQL

Answer 3

Use a parameterized querydef and invoke it from vba.
The query is easier to design...easily testable..and easily accessible from VBA or a form.

dim qd as querydef
set qd = currentdb.querydefs!myquerydef

qd.parameters!parm1=val1

....

either qd.execute

or

dim rs as recordset
set rs = qd.openrecordset()

YMMV ...

Answer 4

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

Answer 5

Another way to do this that it seems no one has mentioned is to bind your graph to a single saved QueryDef and then at runtime, rewrite the QueryDef. Now, I don't recommend altering saved QueryDefs for most contexts, because it causes front-end bloat and is usually not even necessary (most contexts where you use a saved QueryDef can be filtered in one way or other in the context in which they are used, e.g., as a form's Recordsource, you just pass one argument in the DoCmd.OpenForm).

Graphs are different because the SQL driving the graphs can't be altered at runtime.

Some have suggested parameters, but opening a form with a graph on it that uses a SQL string with parameters is going to pop the default parameter dialogs. One way to avoid that is to use a dialog form to collect the criteria and then set the references to the controls on the dialog form as parameters, e.g.:

PARAMETERS [Forms]![MyForm]![ID] Long;

If you're using form references, it's crucial that you do this, because from Access 2002 on, the Jet Expression Service doesn't always correctly process these when the controls are Null. Defining them as parameters rectifies that problem (which was not present before Access XP).

One situation in which you must rewrite the QueryDef for a graph is if you want to allow the user to choose the N in a TOP N SQL statement. In other words, if you want them to be able to choose TOP 5 or TOP 10 or TOP 20, you will have to alter the saved QueryDef, as the N can't be parameterized.

Answer 6

"if you want to allow the user to choose the N in a TOP N SQL statement" -- well, you could use a correlated subquery (instead of dynamic SQL) e.g. (ANSI-92 Query Mode syntax):

CREATE PROCEDURE GetOrdersTopN
(
 :N INTEGER
)
AS 
SELECT O1.OrderDate, O1.CustomerID
  FROM Orders AS O1
 WHERE :N >= (SELECT COUNT(*) + 1
               FROM Orders AS O2
              WHERE O1.OrderDate < O2.OrderDate);

...but last time I checked the Access engine wasn't well optimized (to put it mildly) for this kind of construct.