Solved sp_execute Invoked for Every Row Returned by Query

[PleasantB]

I've upgraded an Access 2003 application to Access 2016 and migrated it's back-end to SQL Server.
I'm experiencing lockups on a particular form which features ~25K records.
Upon running an SQL Server Profiler trace, I noticed that for every row returned by query, an sp_execute query is also being invoked.

Form Record Source:

SELECT PileInfoMain.Batch, PileInfoMain.Stake, PileInfoMain.WasteStream, PileInfoMain.DateStaged, PileInfoMain.SpecialStatus, PileInfoMain.CurrentStatus, PileInfoMain.Comment
FROM PileInfoMain
WHERE (((PileInfoMain.CurrentStatus)="DONE"))
ORDER BY PileInfoMain.Batch;

I tried a few suggestions I found while Googling the issue, to no avail: removing the WHERE clause, removing the ORDER BY clause, and making the query pass-through. Finally, I changed the form's Recordset Type from Dynaset to Snapshot and the sp_execute queries disappeared.

I don't necessarily want to get rid of Dynaset because I want changes made by other users to be reflected without closing and reopening the form.
Is there any way to keep Dynaset without the extraneous sp_execute queries?
Does anyone know why Dynaset causes all of these sp_execute queries?

Thanks in advance!

 

[Minty]

Without trying to sound too blunt, I wouldn't be pulling in 25K records into an editable form. I'm guessing it's a continuous form?
Use a snapshot to present the data and have a method to edit a single record based on which one is selected, this would be the normal way to deal with a large dataset.

Do you have any triggers on the table?

 

[PleasantB]

Thanks for the response.

This is a continuous form but it is not editable. There is a button on each row that opens a single form for editing the record.
When I said "I want changes made by other users to be reflected without closing and reopening the form" I was referring to when another user edits or adds a record via the single form but perhaps Dynaset doesn't provide that anyway.
I may be overthinking this, I haven't worked on Access much in recent years so my knowledge is rusty/foggy.

 

[Minty]

In that case, I would use a snapshot if you are sticking with Access, as it will be much more efficient.
You could force a recordset requery on a timer to keep things fresh.

Spotting edits on records is hard in a snapshot, knowing that records have been added is simpler with a simple recordset count between form opening, and perhaps pressing the button to edit a record you can notify the end-user that data has been added?

The alternative would be SQL view, as that wouldn't normally be editable but should be dynamic. I'm not sure any form will accurately show you changes without a recordset requery.

 

[PleasantB]

I'm going to check with the client to see how important live updates are, it may be that they don't actually care.
Either way I'll stick with Snapshot and implement a mechanism like you suggested for live updating, if need be.

Thanks again for the responses!