'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?

Lxx

What type of cursor does your varConnection use? I believe you need adOpenKeyset or adOpenStatic to get an accurate .RecordCount with ADO. The default adOpenForwardOnly is insufficient for this purpose. – Virginity

@Jeeped I've been using adUseClient (3). I saw it somewhere on the internet, but if adOpenKeyset or adOpenStatic would be better, I'd be willing to try it. – Lxx

Answer 1

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.

Catie

Thanks for the quick reply! I don't like the fact that I would have to work around ADO 2.5's idiosyncrasies, but if ADO 6.0 doesn't work out, this would absolutely be a valid option (I just tested it). – Lxx

Answer 2

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)

Sunfish

Thanks, fixed mine VBA code in Excel & Word & Access after had to upgrade Office 32 => 64 because of Visio... Indeed, I think that this should be marked as the answer. – Aqueous

been a while since I looked at this one, but I am glad it was able to help you out. Cheers – Sunfish

Answer 3

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.

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>

Lxx

Saved my bacon. Cheers! Upgrading from 2.8 to 6.1 allowed me to correctly assigned a Long variable called RecordCount to the rs.RecordCount property – Monocyte

Answer 4

I change it from as Long to as LongLong Then my VBA starts to work. No hotfix needed...

Tremor

Answer 5

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:

IF intNumRecs > CInt(DBreports.RecordCount) THEN...

Enkindle

Answer 6

Just check the version of Excel. ADO works well 32 bit and teething issues with 64 bit.

Spirula