SQL 记录计数 VBA ADODB 连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25509609/
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
SQL Record Count VBA ADODB connection
提问by Jimjebus
Trying to work a RecordSet count in VBA Using ADODB recordset but it won't seem to get the count to work properly.
尝试使用 ADODB 记录集在 VBA 中处理 RecordSet 计数，但它似乎无法使计数正常工作。
I've got 50 records in a worksheet with unique ID's, some of them are already in a database and some are not, the code itself is to loop through each cell and get the value of the cell and run that through an SQL select statement if theres a hit then say found if not then say nothing.
我在具有唯一 ID 的工作表中有 50 条记录，其中一些已经在数据库中，有些不在，代码本身就是遍历每个单元格并获取单元格的值并通过 SQL 选择语句运行它如果有命中，则说找到，如果没有，则什么也不说。
Unfortunately it seems to return the same result for everything, even the id's i know do not exist.
不幸的是，它似乎对所有内容都返回相同的结果，即使我知道的 id 也不存在。
Code bellow
代码如下
Sub NO6_ChequeCheck()
Dim con As ADODB.Connection
Dim rec As ADODB.Recordset
Set con = New ADODB.Connection
Set rec = New ADODB.Recordset
Dim sql As String
Dim client As String
Dim myRange As Range
Dim myCell As Range
Set myRange = Range("A2:A52")
Dim i As Integer
i = 2
With con
.Provider = "MSDASQL"
.ConnectionString = "DSN=localhostTest"
.Open
End With
For Each myCell In myRange
client = myCell.text
sql = "SELECT * FROM crm_client_cheques WHERE id = '" & client & "' "
rec.Open sql, con
If rec.RecordCount = 0 Then
Cells(i, "H").Value = "Nothing"
Else
Cells(i, "H").Value = "Found"
End If
rec.Close
i = i + 1
Next myCell
The main thing I've come across is that if i toggle that 0 to say 50 and mess with the = sign and change it to < or > then the results will change to either , which leads me to believe its not resetting the recordcount back so it just stacks each time so its always going up each loop and not going back to 0.
我遇到的主要问题是，如果我将 0 切换为 50 并弄乱 = 符号并将其更改为 < 或 > 那么结果将更改为任一，这使我相信它不会重置记录计数所以它每次只是堆叠，所以它总是在每个循环上升而不是回到 0。
i have tried telling it to close and equal nothing and moved various bits of code around but nothing helps much.
我试图告诉它关闭和等于什么，并移动了各种代码位，但没有任何帮助。
All help appriciated
所有帮助appriciated
回答by george
You need to check whether there are any records returned by your sql query. You can do that by
您需要检查您的 sql 查询是否返回任何记录。你可以这样做
If Not (rec.BOF And rec.EOF) Then 'There are no records
Cells(i, "H").Value = "Nothing"
else
Cells(i, "H").Value = "Found"
End if
