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):
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 .RecordCount
of 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.
Edit 1: Just wanted to clarify that I was previously using ADO 2.5 NOT ADO 6.1
TL;DR: How can I fix a RecordSet.RecordCount
"Type Mismatch" error on a 64-bit machine running Excel 2010?
this issue is actually caused by a bug in earlier excels. there is a hotfix out there. HotFix
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
rst = SomeRecordset
dim rstCount as Long
rstCount = CLng(rst.RecordCount)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With