type mismatch error in Access after upgrading to Office 2021

Question

After recently upgrading from Office 2010 to Office 2021, a report fails with a type mismatch error. I've made no changes to the code, and it still works in Access 2010. In addition to the code below, RecCount is declared Public Integer.

The references are: Visual Basic For Applications; Microsoft Access 16.0 Object Library; OLE Automation; Microsoft Outlook 16.0 Library; Microsoft ActiveX Data Objects 2.1 Library.

Any suggestions would be greatly apreciated.

Answer 1

from this Microsoft documentation: RecordCount Property (ADO) - ActiveX Data Objects (ADO) | Microsoft Docs

Return Value

Returns a Long value that indicates the number of records in the Recordset.

That's clearly a datatype mismatch.

Answer 2

"I've made no changes to the code, and it still works in Access 2010. "

For better or worse, it not at all unusual to find that newer versions of Access tighten up on various requirements. Here, it appears to be the case that what you got away with in 2010 (Integer vs Long) is no longer valid.

Change the Dim to Long and see if that resolves the problem.

On a related note, the recent recordset bug I referred to was related to field types in Recordsets, so not relevant here. Still it does point out that change is to be expected.

Answer 3

I changed it, as shown below, but still the same error.

Option Compare Database

Option Explicit

Public NxtPgNum As Integer, RecCount As Long, MlgRate As Currency

Answer 4

Anybody have any other suggestions?

Answer 5

LarryA15,

Ok, the declared "RecCount" is there I assumed.

There is an issue with this "RecordCount" when using ADO. I'm not sure it was fix the last time about 5 years ago.

Try converting the "RecordCount" properties.

Try moving records back and forwards.

RecCount.MoveLast

RecCount.MoveFirst

RecCount = Clng(rstRec.RecordCount)