How to get data from SQL query in Microsoft Access VBA?

Question

Hey, I just learned how to put my SQL queries in VBA (or at least write them), but I have no idea how to return the data?

I have several forms (chart forms) based on queries in which I run fairly regular parameters by simply changing the timeframe (for example, the top 10 sales in a month). Then I have procedures that automatically transfer the chart object to the PowerPoint presentation. So, I have all these queries pre-built (for example, 63), and the diagram forms correspond (yes, yes ... 63 ... I know this is bad), and then all these things are set to "open / close "events triggering the next one (this is like my best attempt at becoming a hack .... or domino, whichever you prefer).

So, I was trying to learn how to use SQL statements in VBA so that in the end I could do all this (I still need to store all these chart forms, but I don’t know, because I clearly lack understanding).

So, besides the question I asked above, can anyone offer advice? thanks

Answer 1

This is a bit outdated, so you might want to take a book on this subject . But here is a ton of access resources and some tutorials and examples . 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 

For comment: look at the record set class . It contains a collection called "Fields", which are the columns returned from your query. Not knowing your circuit, it's hard to say, but something like ...

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

As I said, it’s best to take a book on this subject, they have many examples.

Answer 2

Use a parameterized querydef and call it from vba.
The request is easier to design ... easily verifiable and easily accessible from VBA or 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 3

Here is a function that you can consider refactoring, take a line, and you can reuse anywhere in your code.

So, you have CONST or build a line for your SQL statement and enter the SANITIZED, NON SQL INJECTED argument in the line :)

i.e.

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

... then call the / sub function from anywhere you need to get the record / data / execute the statement. Consider creating several data access / access functions in which you can simply run the UPDATE statement or get a single value or get a complete list of records.

The key here is to have all these functions work in one place and use them everywhere. 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 4

Despite the complexity, your path is easier than trying to create SQL statements in VBA. Remember that saving queries individually allows you to visually manipulate them. In addition, there are some performance benefits.

A better understanding of SQL will help you consolidate and simplify existing queries (if you even need to simplify them. It seems that they work a lot, so you might need all 64 queries).

However, it is fairly 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 5

Another way to do this, which no one has mentioned to you before, is to associate your graph with one saved QueryDef, and then rewrite QueryDef at runtime. Now I do not recommend changing saved QueryDefs for most contexts, because it causes foreground bloating and is usually not required (most contexts in which you use saved QueryDef can be filtered anyway in the context in which they are used, for example, as a form Recordsource, you just pass one argument to DoCmd.OpenForm).

Graphs are different because the SQL that controls the graphs cannot be modified at runtime.

Some of them suggested parameters, but when you open a form with a graph on it that uses an SQL string with parameters, you will open the default parameter dialogs. One way to avoid this is to use the dialog form to collect criteria, and then set the links to the controls in the dialog form as parameters, for example:

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

If you use form links, it is important that you do this because from Access 2002 on, the Expression Expression service does not always correctly handle them when the controls are Null. Defining them as parameters eliminates this problem (which was not the case before Access XP).

One situation in which you must rewrite QueryDef for a graph is if you want to allow the user to select N in the TOP N SQL statement. In other words, if you want them to be able to select TOP 5 or TOP 10 or TOP 20, you will have to change the saved QueryDef, since N cannot be parameterized.

Answer 6

"if you want to allow the user to select N in the TOP N SQL statement" - well, you can use a correlated subquery (instead of dynamic SQL), for example. (ANSI-92 Request 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 that the access mechanism was not well optimized (to put it mildly) for this type of construction.