'Type Mismatch' Error on ADODB.Recordset

adodb, excel, recordset, vba

Solution

I haven't had this exact problem, but I've found that the `recordcount` property on an ADODB recordset is hit or miss. Your best bet is to rewrite the loops like:

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.

Problem

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?

Original source