如何从 Microsoft Access VBA 中的 SQL 查询中获取数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1115058/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I get the data from an SQL query in microsoft Access VBA?
提问by Justin
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?
嘿，我刚刚学会了如何将我的 SQL 语句放入 VBA（或至少将它们写出来），但我不知道如何返回数据？
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).
我有几个基于查询的表格（图表表格），我针对这些查询运行了非常常规的参数，只是改变了时间范围（比如当月的前 10 名销售额）。然后我有自动将图表对象传输到PowerPoint演示文稿的程序。所以我预先构建了所有这些查询（如 63），并匹配图表形式（呃，是的......63......我知道这很糟糕），然后所有这些东西都设置在“打开/关闭”事件触发下一个（这就像我最好的尝试成为黑客......或多米诺骨牌；无论你喜欢哪个）。
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).
所以我试图学习如何在 VBA 中使用 SQL 语句，以便最终我可以在那里完成所有这些（我可能仍然需要保留所有这些图表形式，但我不知道，因为我显然缺乏理解）。
So aside from the question that I asked at the top, can anyone offer advice? thanks
所以除了我在顶部提出的问题之外，有人可以提供建议吗？谢谢
回答by JP Alioto
It's a bit dated, so you might want to grab a book on the subject. But, here's a ton of access resourcesand some tutorials and examplesas 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 ...
每条评论：看看记录集类。它包含一个名为 Fields 的集合，这些集合是从您的查询返回的列。不知道你的架构，很难说，但像......
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.
就像我说的，你最好的办法是找一本关于这个主题的书，他们有很多例子。
回答by CMB
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.
使用参数化的 querydef 并从 vba 调用它。
查询更容易设计......易于测试......并且可以从VBA或表单轻松访问。
dim qd as querydef
set qd = currentdb.querydefs!myquerydef
qd.parameters!parm1=val1
....
....
either qd.execute
要么 qd.execute
or
或者
dim rs as recordset
set rs = qd.openrecordset()
YMMV ...
YMMV ...
回答by p.campbell
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 :)
因此，为您的 SQL 语句设置一个 CONST 或构建一个字符串，并将您的 SANITIZED、NON SQL INJECTED 字符串作为参数弹出 :)
i.e.
IE
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.
...然后从任何需要获取记录集/数据/执行语句的地方调用函数/子。考虑创建一些数据访问函数/子程序，您可以在其中简单地运行 UPDATE 语句，或检索单个值，或检索完整的记录集。
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.
这里的关键是让这些功能都集中在一个地方，并在所有地方重复使用。这是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
回答by David-W-Fenton
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).
另一种似乎没有人提到过的方法是将您的图形绑定到单个保存的 QueryDef，然后在运行时重写 QueryDef。现在，我不建议为大多数上下文更改已保存的 QueryDefs，因为它会导致前端膨胀并且通常甚至没有必要（大多数使用已保存 QueryDef 的上下文可以在它们所在的上下文中以一种或其他方式进行过滤）例如，用作表单的记录源，您只需在 DoCmd.OpenForm 中传递一个参数）。
Graphs are different because the SQL driving the graphs can't be altered at runtime.
图是不同的，因为驱动图的 SQL 不能在运行时改变。
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.:
有些人提供了建议的参数，但打开一个带有图形的表单，该表单使用带参数的 SQL 字符串将弹出默认参数对话框。避免这种情况的一种方法是使用对话框表单来收集条件，然后将对话框表单上的控件的引用设置为参数，例如：
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).
如果您正在使用表单引用，那么执行此操作至关重要，因为从 Access 2002 开始，当控件为 Null 时，Jet Expression Service 并不总是正确处理这些。将它们定义为参数可以解决该问题（在 Access XP 之前不存在）。
One situation in which you mustrewrite 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.
您必须为图形重写 QueryDef 的一种情况是，如果您希望允许用户在 TOP N SQL 语句中选择 N。换句话说，如果您希望他们能够选择 TOP 5 或 TOP 10 或 TOP 20，则必须更改保存的 QueryDef，因为 N 无法参数化。
回答by onedaywhen
"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):
“如果你想让用户在 TOP N SQL 语句中选择 N”——好吧，你可以使用相关子查询（而不是动态 SQL），例如（ANSI-92 查询模式语法）：
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.
...但上次我检查 Access 引擎并没有针对这种构造进行很好的优化（委婉地说）。
回答by Robert Harvey
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.
尽管很复杂，但您的方法比尝试在 VBA 中构建 SQL 语句更容易。请记住，单独保存查询允许您直观地操作它们。此外，还有一些性能优势。
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).
更好地理解 SQL 将帮助您整合和简化现有查询（如果它们甚至需要简化。听起来他们正在做很多工作，所以无论如何您可能需要所有 64 个查询）。
That said, it's pretty easy to execute SQL queries in code:
也就是说，在代码中执行 SQL 查询非常容易：
Dim strSQL as String
strSQL = "UPDATE Table MyTable SET fieldname = 1 WHERE fieldname = 0;"
DoCmd.RunSQL strSQL
