It could be a disconnect between DAO and ADO. The recordsets behind Access forms are naturally DAO so I'm not sure how replacing a DAO query with ADO actually works.
The Recordset property of the form is simply Set to the ADO Recordset. I have used ADO stand alone recordsets before and Count worked fine though I hadn't used Sum on them. I assumed the Sum function would work too but it seems to be expecting DAO for some reason, at least when the recordset is connected.
I will try some experiements today and report back. Maybe it is related to the recordset type or cursor type.
Just out of curiosity, why did you change the form? In my years of working with Access, I have found that using the Access "style" created simpler and more stable applications because I didn't have to write code to fight the built in methods.
The MS SQL Server table has over 26 million records and growing by several thousand per day. A query to the ODBC linked table sometimes took a second or two. The recordset returned from the ADO command (connected via OLEDB) running a parameterised stored procedure is quite literally instant.
The performance using LinkMasterFields and LinkChildFields was terrible (several seconds) when I first moved the backend to MS SQL Server. It had been reasonable (couple of seconds) using Link Fields when the table was in an Access Back End and held less then ten million records.
After the move, abandonning the Link Fields and running a dynamic query to the linked table OnCurrent brought it back down to a second or two again depending partly on the number of records but otherwise the time seemed somewhat random.
Another alternative would have been a dynamic PassThrough query. However this still has the hesitation while the server generates an execution plan each time. The Parameterised Stored Procedure does not have this overhead an consistently loads the form in less than a blink of an eye.
As a workaround I will have the server calculate the Sums in the Stored Procedure and return them as output parameters. Another alternative would be to run a loop through the recordset but I would expect it is better to give the job to the server if the performance of the query is any indication.