ADO error or bug - Type Mismatch

Question

Running on Access 2010 and Windows on 64bit.

Using 32bit APIs with PtrSafe.

Both older and newer References

Microsoft ActiveX Data Objects 6.0 Library

Microsoft ADO Ext. 6.0 for DDL and Sercurity

Or

Microsoft ActiveX Data Objects 2.8 Library

Microsoft ADO Ext. 2.8 for DDL and Sercurity

No luck with both.

Here the problem.

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim strSQL As String

Dim I as Integer

Set cn = CurrentProject.Connection

Set rs = New ADODB.Recordset

strSQL = “Select * From MyTable Where SomeID = 1”

Rs.open “strSQL”, cn, adOpenStatic,adLockOptimistic

For I = 0 To rs.RecordCount

So on……

There are two problems here. 1 is “Set rs = New ADODB.Recordset and 2 is rs.RecordCount

“rs.RecordCount” shows the error

Compiled error: Type mismatch

I tried changing it to “Set rs = ADODB.Recordset”, errored with a message

Compiled error: Method or data member not found.

The other problem was VBA pointed the error at “+” on the line lngCount = rs.RecordCount + 1 in another form. But I believed it’s due to “rs.RecordCount” error as the “Type mismatch”.

Has anyone got clues? I’m thinking that ADO is only working in 32bit version only. I have yet to test it on a 32bit system with 32bit Access 2010 or Microsoft Access Team missing something here. (unless I can set it up)

---

Please Mark As Answered if it solved the problem.

Answer 1

I use Windows 8 64bit - Office 2010 64bit

for i = 1 to rs.recordcount works on 32bit systems but not on my 64bit system

On my 64 bit system, I have a recordset where the recordcount is 2. 

Viewing the data in rs.recordcount shows recordcount = 2^

Convert that to an integer and it works flawlessly on both 32bit and 64bit.

for i = 1 to CInt(rs.recordcount)

Hope this helps.

Answer 2

The code extract you shown works without error (and the only ADDITIONNAL reference you need is Microsoft ActiveX Data Objects 6.0 Library). Can you test it on a blank database  (you need a table table1) :

Public Sub TestADO()

Dim rs As ADODB.Recordset

  Set rs = New ADODB.Recordset

  rs.Open "SELECT * FROM table1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

  Dim i As Long

  For i = 0 To rs.RecordCount - 1

  Next i

End Sub

Answer 3

Hello,

By now i hope you had solved your problem, but for other readers that may face the same issue a solution i'd found was to:

1. Add a reference to Microsoft Activex Data Objects 6.x and
2. Add a reference to Microsoft Activex Data Objects Recordset Library 2.8

My environment at this time is:

Win 7 SP1 x64

MS Office 2k13

G'd luck

Answer 4

Thanks Vanderghast,

No didn't work, it refused to compile. The line errored at

Let limit = rs.RecordCount

The problem was this highlight ".RecordCount".

PS. Vanderghast, There were other postings mentioning about lowering the ADO version. That works too. It seems that version 2.8 and 6.0 is having problems. I lowered to 2.1 version if you want to know.

I hope the Access Team is reading this.

---

Please Mark As Answered if it solved the problem.

Same thing with RecordCount and 64-bit Access 2010 and Windows 7. 

Lowering to ADO 2.1 didn't work.

Raising ADO version to 6.1 did the trick.

Answer 5

Vanderghast,

Ok, here's what I found. It seems that Windows7/Access2010 64-bit (not WOW64 in case you're confused) cause a bug.

This "For i = 0 To rs.RecordCount - 1" or this "For i = 0 To rs.RecordCount" will never work.

A temp solution would be

For i = 0 To rs.RecordCount &"" or For i = 0 To (rs.RecordCount & "")

Here's another that would also work

Do until rs.eof

debug.print rs.numberid & " - " rs.recordcount

rs.movenext

loop

Another would be

lngCount = lngCount + (rs.recordcount & "") + 1

If anyone have a better solution, please let us know.

---

Please Mark As Answered if it solved the problem.