vba ADOBE.recordset 过滤器/查找
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19495446/
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
vba ADOBE.recordset filter/find
提问by iBG
I have a ADOBE.Recordset in Excel VBA returned from a query to database. How should I find a certain record in this set that fits certain criteria? Below is the code. Could anyone fill in the " 'print out the name of one person whose age is i" part for me? Thanks in advance!
我在 Excel VBA 中有一个 ADOBE.Recordset 从查询返回到数据库。我应该如何在这个集合中找到符合特定标准的特定记录？下面是代码。谁能帮我填写“'打印一个年龄是我的人的名字”部分？提前致谢！
Dim rs As ADOBE.Recordset
q = "select name, age from people where country = 'US'"
Set rs = conn.Execute(q) 'conn is an ADOBE.Connection
For i = 30 To 40
'print out the name of one person whose age is i
Next i
Update 1:Thanks KazJaw! I think your solutions should work. However, I am looking for a cleaner solution -
更新 1：感谢 KazJaw！我认为您的解决方案应该有效。但是，我正在寻找更清洁的解决方案-
- I don't want to save the query results into a sheet. I'd prefer them in memeory.
- Is there a .Find or .Search function I can use so that I don't need to implement the search with a loop (as you did in the Second Solution)?
- 我不想将查询结果保存到工作表中。我更喜欢他们的记忆。
- 有没有我可以使用的 .Find 或 .Search 函数，这样我就不需要用循环来实现搜索（就像你在第二个解决方案中所做的那样）？
Maybe I am being greedy here, but ideally, I'd like something like this:
也许我在这里很贪心，但理想情况下，我想要这样的东西：
Dim rs As ADOBE.Recordset
q = "select name, age from people where country = 'US'"
Set rs = conn.Execute(q) 'conn is an ADOBE.Connection
For i = 30 To 40
name = rs.Find("age = i")!name 'this line is where I am not sure how to achieve
MsgBox name & "'s age is " & i
Next i
Apologies for the formatting. I am new to the site, not sure how to properly indent the two lines in the For loop.
为格式道歉。我是该网站的新手，不确定如何正确缩进 For 循环中的两行。
Update 2:
更新 2：
Yes KazJaw, other problem rises. ".Find" requires rs to be able to scrolled back, which requires its lockType to be set to adLockOptimistic. Haven't figured out how yet. Will post if I do.
是的 KazJaw，还有其他问题。“.Find”要求 rs 能够向后滚动，这需要将其 lockType 设置为 adLockOptimistic。还没想好怎么弄 如果我会发布。
Solution:The Key is to use rs.Open instead of conn.Execute and to set CursorType.
解决方法：关键是用rs.Open代替conn.Execute，设置CursorType。
Dim rs As ADOBE.Recordset
q = "select name, age from people where country = 'US' Order By i"
Set rs = New ADODB.Recordset
rs.Open Source:=q, CursorType:=adOpenStatic, ActiveConnection:=ThisWorkbook.conn 'conn is an ADOBE.Connection
For i = 30 To 40
name = rs.Find("age = i")!name 'this line is where I am not sure how to achieve
MsgBox name & "'s age is " & i
Next i
采纳答案by Kazimierz Jawor
First solution,without looping, you could do it in this way but you need to stick to @mehow suggestion where age conditionshould be implemented in SQL query.
第一个解决方案，没有循环，你可以这样做，但你需要坚持@mehow建议age condition应该在SQL查询中实现的位置。
'return all results as of cell A2, direction down+right, in activesheet
ActiveSheet.Range("A2").CopyFromRecordset rs
Second solution,with looping, instead of your For i...Nextloop try below solution.
第二种解决方案，使用循环，而不是你的For i...Next循环尝试下面的解决方案。
Dim lRow as long
lRow=2
With rs
Do Until .EOF
'return only those which age equals to i
'if implemented in SQL query then you could get rid of if statement below
if .Fields(1).Value = i then
Cells(lRow, 1) = .Fields(1).Value
Cells(lRow, 2) = .Fields(2).Value
.MoveNext
lRow = lRow + 1
end if
Loop
End With
Third solution.If you really need to use .Find methodthen do it in this way:
第三个解决方案。如果您确实需要使用，.Find method请按以下方式操作：
'...your loop here
rs.Find "age = " & i
name = rs(0)
MsgBox name & "'s age is " & i
'... rest of your code here
Unfortunately, I'm not sure if it will work. I think you will need to sort your results by age within SQL code. If not I expect some of the ages can be omit. Some other problems could arise. Therefore try with other solutions.
不幸的是，我不确定它是否会起作用。我认为您需要在 SQL 代码中按年龄对结果进行排序。如果不是，我希望可以省略一些年龄。可能会出现其他一些问题。因此尝试使用其他解决方案。
