SQL 如何通过vba查看访问表中的记录集？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5182087/
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 to view a recordset in an access table by means of vba?
提问by JohnMunich
With help of the embedded access vb editor i've written a small code to analyse the field values of my database, and want to finally view the recordsets in a table inside the opened access. As a newbie i can only use Debug.Print to display the field names. Could anyone of you tell me with which statements/commands i can execute my SQL String in order to view the result recordset with values?
在嵌入式访问 vb 编辑器的帮助下，我编写了一个小代码来分析我的数据库的字段值，并希望最终在打开的访问中查看表中的记录集。作为新手，我只能使用 Debug.Print 来显示字段名称。你们中的任何人都可以告诉我我可以使用哪些语句/命令来执行我的 SQL 字符串以查看带有值的结果记录集？
Debug.Print FinalSQLString
回答by rskar
Here is the basic recipe:
这是基本配方：
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM myTable")
Know that you are using Jet Data Access Objects (DAO) with Access - google that for details.
知道您正在将 Jet 数据访问对象 (DAO) 与 Access 结合使用 - 谷歌了解详情。
Expression (rs.BOF and rs.EOF) = Trueindicates there were no rows.
表达式(rs.BOF and rs.EOF) = True表示没有行。
Use rs.MoveFirst, rs.MoveNextto go to the first and next rows. Test rs.EOFafter rs.MoveNext; when True, last row was already processed.
使用rs.MoveFirst,rs.MoveNext转到第一行和下一行。rs.EOF之后测试rs.MoveNext；当True，最后一行已经被处理。
rs(FieldName)returns the value of the column named FieldName (a string expression).
rs(FieldName)返回名为 FieldName 的列的值（字符串表达式）。
rs(1)returns the value of the second column.
rs(1)返回第二列的值。
When done, rs.Close.
完成后，rs.Close。
There is no way to hand Access the RecordSet and have it displayed in a Datasheet view. Instead, you will have to create a QueryDef object and use it to perform the query and display the Datasheet view of the results:
无法手动访问 RecordSet 并将其显示在数据表视图中。相反，您必须创建一个 QueryDef 对象并使用它来执行查询并显示结果的数据表视图：
Dim qd As QueryDef
On Error Resume Next
CurrentDb.QueryDefs.Delete "temp"
On Error GoTo 0
Set qd = db.CreateQueryDef("temp", "SELECT * FROM myTable")
DoCmd.OpenQuery "temp", acViewNormal, acEdit
回答by Patrick Honorez
As far as I know, there is no way to display a datasheet containing a VBA instance of a recordset. If the source of your recordset is strQSL, you could however create a table with your results, and open that one, or more elegantly, create queryDef and open it:
据我所知，无法显示包含记录集 VBA 实例的数据表。如果您的记录集的来源是 strQSL，那么您可以创建一个包含您的结果的表，然后打开该表，或者更优雅地，创建 queryDef 并打开它：
Sub ShowQd(strQdName As String, strSql As String)
'creates queryDef and display it in a datasheet'
Dim qd As DAO.QueryDef
Set qd = CurrentDb.CreateQueryDef(strQdName)
With qd
.ReturnsRecords = True
.SQL = strSql
End With
DoCmd.OpenQuery strQdName
End Sub
If you focus on displaying things, you could also put a ListBox in a form, set its Number of columns to the number of fields returned by your query (qd.Fields.Count), and set your strSql as the RowSource of the ListBox. AND... if you put all your related code in that form, you now have a form that you can import in any db to quickly display what you want :)
Good luck !
如果您专注于显示内容，您还可以在表单中放置一个 ListBox，将其列数设置为您的查询返回的字段数 ( qd.Fields.Count)，并将您的 strSql 设置为 ListBox 的 RowSource。并且...如果您将所有相关代码都放在该表单中，您现在就有了一个表单，您可以将其导入任何数据库以快速显示您想要的内容:)
祝您好运！
