如何从Microsoft Access VBA中的SQL查询获取数据? [英] How do I get the data from an SQL query in microsoft Access VBA?

问题描述

嘿，我只是学习了如何将我的SQL语句放入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?

我有一些基于查询的表格(图表形式)，我针对这些查询运行常规参数，只是更改了时间范围(例如本月排名前10位的交易).然后，我有一些将图表对象自动传输到PowerPoint演示文稿中的过程.因此，我已经预先构建了所有这些查询(例如63)，并且要匹配图表格式(嗯，是的.... 63 ...我知道这很不好)，然后所有这些事情都在"open/关闭"事件触发下一个事件(就像我尽最大努力尝试成为骇客....或多米诺骨牌；无论您喜欢哪个).

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).

所以我试图学习如何在VBA中使用SQL语句，以便最终可以在那里完成所有这些操作(我可能仍需要保留所有这些图表形式，但我不知道，因为我显然缺乏理解)

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

推荐答案

有些过时了，因此您可能想抢购访问资源和一些教程和示例.但是，基本上...

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.

这篇关于如何从Microsoft Access VBA中的SQL查询获取数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！