访问：在 VBA 中使用查询记录集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10539836/
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
Access: Using query in VBA for recordset
提问by Rick
I have been accustomed to do recordssets in the following format:
我已经习惯于以下列格式做记录集：
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "Select field1, field2 from myTable where field1 > 30"
Set rs = CurrentDb.OpenRecordset(strSQL)
'... Do wahtever using rs.
Is it possible to use an already created query instead of text and giving it the where clause?
是否可以使用已经创建的查询而不是文本并为其提供 where 子句？
This is a linked table to a SQL Server 2008 Database. I like to save simple queries in Access.
这是到 SQL Server 2008 数据库的链接表。我喜欢在 Access 中保存简单的查询。
回答by Conrad Frix
You can either
你可以
Use a query that has parameters and specify values for parameters provided that the query uses parameters.
Dim dbs As DAO.Database Dim qdf As DAO.QueryDef Dim prm As DAO.Parameter Dim rst As DAO.Recordset Set qdf = CurrentDb.QueryDefs("qry_SomeQueryWithParameters") qdf.Parameters("SomeParam").Value = "whatever" Set rst = qdf.OpenRecordset
使用具有参数的查询并指定参数值，前提是该查询使用参数。
Dim dbs As DAO.Database Dim qdf As DAO.QueryDef Dim prm As DAO.Parameter Dim rst As DAO.Recordset Set qdf = CurrentDb.QueryDefs("qry_SomeQueryWithParameters") qdf.Parameters("SomeParam").Value = "whatever" Set rst = qdf.OpenRecordset
or
或者
- Specify a query name as the command and use the Filter propertyon the recordset
- 指定查询名称作为命令并使用记录集上的过滤器属性
Dim rs As DAO.Recordset
Dim rsFiltered As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(qry_SomeQueryWithoutParameters)
rs.Filter = "field1 > 30"
set rsFiltered = rs.OpenRecordset
