'Type Mismatch' Error on ADODB.Recordset

Question

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?

Answer 1

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)