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

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

sql ms-access vba

Edit Report

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.

answered 2009-07-12T20:26:02.780

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

1 Answer

Your Answer