vba ADODB.Recordset 上的“类型不匹配”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28729023/
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
'Type Mismatch' Error on ADODB.Recordset
提问by jaysoncopes
I have a program that is supposed to read data from an SQL database and report back to Excel. It works as expected on a 32-bit machine, but since I moved over to a 64-bit work environment, the program has failed to run. Here is a sample of my code (the first error returned):
我有一个程序应该从 SQL 数据库读取数据并向 Excel 报告。它在 32 位机器上按预期工作，但自从我转移到 64 位工作环境后，该程序无法运行。这是我的代码示例（返回的第一个错误）：
Private Sub SearchBox_Change()
ResultBox.Clear
Call CompileQuery
'If the query is empty
If SearchBox.Value = "" Then
NumShowingLabel = "Showing 0 of 0 Results"
ResultBox.Clear
GoTo noSearch
End If
'Open a new query with varQuery
With varRecordset
.ActiveConnection = varConnection
.Open varQuery
End With
'Set NumShowingLabel
If varRecordset.RecordCount > varMaxResults Then
NumShowingLabel = "Showing 60 of " & varRecordset.RecordCount & " Results"
Else
NumShowingLabel = "Showing " & varRecordset.RecordCount & " of " & varRecordset.RecordCount & " Results"
End If
'As long as there is a record, move to the first one
If Not varRecordset.RecordCount = 0 Then varRecordset.MoveFirst
'Add each record to ResultBox
If varRecordset.RecordCount > varMaxResults Then
For varTempInt = 1 To varMaxResults
ResultBox.AddItem varRecordset.Fields("FileName").Value
varRecordset.MoveNext
Next
Else
For varTempInt = 1 To varRecordset.RecordCount
ResultBox.AddItem varRecordset.Fields("FileName").Value
varRecordset.MoveNext
Next
End If
'Release varRecordSet
varRecordset.Close
noSearch:
End Sub
When run, Excel returns an error "Type Mismatch" and highlights .RecordCountof For varTempInt = 1 To varRecordset.RecordCount(the last for loop in the sample). I have installed the hotfix recommended by the Windows Support Article 983246, at least to the best of my understanding. I installed it to the C: directory and restarted my machine, but it still does not work.
当运行时，Excel返回错误“类型不匹配”和亮点.RecordCount的For varTempInt = 1 To varRecordset.RecordCount（最后一个for循环的样品中）。我已经安装了 Windows 支持文章 983246 推荐的修补程序，至少就我的理解而言。我将它安装到 C: 目录并重新启动了我的机器，但它仍然无法正常工作。
Edit 1: Just wanted to clarify that I was previously using ADO 2.5 NOT ADO 6.1
编辑 1：只是想澄清一下我以前使用的是 ADO 2.5 而不是 ADO 6.1
TL;DR: How can I fix a RecordSet.RecordCount"Type Mismatch" error on a 64-bit machine running Excel 2010?
TL;DR：如何RecordSet.RecordCount在运行 Excel 2010 的 64 位计算机上修复“类型不匹配”错误？
采纳答案by JNevill
I haven't had this exact problem, but I've found that the recordcountproperty on an ADODB recordset is hit or miss. Your best bet is to rewrite the loops like:
我没有遇到过这个确切的问题，但我发现recordcountADODB 记录集上的属性被命中或未命中。最好的办法是重写循环，如：
recordset.movefirst
While Not recordset.eof
<your stuff with your record>
recordset.movenext
Loop
Also, to test that there are records in your recordset you can use:
此外，要测试您的记录集中是否有记录，您可以使用：
If recordset.BOF and recordset.EOF THEN
<Something is wrong there are no records>
End If
My guess is that the ADODB recordcount property is probably crap with the 64 bit version of whatever ODBC driver you are using as it is in nearly every ODBC driver.
我的猜测是 ADODB 记录计数属性对于您使用的任何 ODBC 驱动程序的 64 位版本可能都是废话，因为它几乎在每个 ODBC 驱动程序中都是如此。
回答by dfresh22
this issue is actually caused by a bug in earlier excels. there is a hotfix out there. HotFix
这个问题实际上是由早期 excel 中的错误引起的。有一个修补程序。热修复
I develop some macros on office 16, but when I do UAT on previous versions, it fails, a quick easy solution for this is simply to cast the RecordCount
我在 office 16 上开发了一些宏，但是当我在以前的版本上执行 UAT 时，它失败了，一个快速简单的解决方案就是简单地投射 RecordCount
rst = SomeRecordset
dim rstCount as Long
rstCount = CLng(rst.RecordCount)
回答by jaysoncopes
Thank you guys for your quick replies, however, I somehow managed to get the idea of using ADO 6.1 instead of ADO 2.5. It appears that using a more up-to-date version of ActiveX Database Objects did the trick, duh.
谢谢你们的快速回复，但是，我以某种方式设法获得了使用 ADO 6.1 而不是 ADO 2.5 的想法。看来使用更新版本的 ActiveX 数据库对象可以解决问题，呵呵。
For future reference, if you are going to upgrade to ADO 6.0, the ConnectionString value will be the same EXCEPT you must use User ID=<USR>; Password=<PSWD>instead of USR=<USR>;PWD=<PWD>
为了将来参考，如果您要升级到 ADO 6.0，ConnectionString 值将是相同的，除了您必须使用User ID=<USR>; Password=<PSWD>而不是USR=<USR>;PWD=<PWD>
回答by Jonson Tsai
I change it from as Long to as LongLong Then my VBA starts to work. No hotfix needed...
我将它从 as Long 更改为 as LongLong 然后我的 VBA 开始工作。不需要修补程序...
回答by Ashwin
Just check the version of Excel. ADO works well 32 bit and teething issues with 64 bit.
只需检查Excel的版本。ADO 在 32 位上运行良好，在 64 位上运行良好。
回答by Bobznkazoo
We had this error due to the same type of comparison and used the same sort of answer as from dfresh22 and Jonson Tsai. Thanks folks!
The only difference for us was that the error was occurring on 64-bit Office and we still have several users on 32-bit Office so it was easier to convert down to the smaller 32-bit variable instead of up to the larger 64-bit variable. Since the variable we were comparing against RecordCount will always be less than 100 (and definitely always < 32K) I was able to just Convert from Long to Integer in the 64-bit version and 32-bit code would just convert from Integer to Integer:
由于相同类型的比较，我们遇到了这个错误，并使用了与 dfresh22 和 Jonson Tsai 相同的答案。谢谢各位！
对我们来说唯一的区别是错误发生在 64 位 Office 上，我们仍然有几个用户使用 32 位 Office，因此更容易转换为较小的 32 位变量，而不是转换为较大的 64 位变量多变的。由于我们与 RecordCount 比较的变量将始终小于 100（并且绝对始终小于 32K），因此我只能在 64 位版本中从 Long 转换为 Integer，而 32 位代码将仅从 Integer 转换为 Integer：
IF intNumRecs > CInt(DBreports.RecordCount) THEN...
如果 intNumRecs > CInt(DBreports.RecordCount) THEN...
