Sql-server – Access ODBC reserved error 7711

The only way to get complete control over the query for ODBC is to create them as pass-through queries.

In that mode, Access will not touch them and the SQL will be passed verbatim to the server (so your queries will need to be written in that server's particular SQL syntax or you'll get errors).

In ODBC mode, Access will decompose queries that are bound to controls into batches so it never returns more results than it need to display (that is what you see: it will decompose queries based on the primary key to return specific records only).

So you have to be careful of what you wish: if you optimise your queries using pass-through, you will get less calls, but all the data will be returned.

On the other hand, if you leave Access do the querying for you, it may be chattier, but the net performance may be better since less data may be returned more quickly.

You need to test both and choose what works best depending on the exact query and how it is used.

I had a very similar problem where I had a query that would run, return 541 rows (every time) and then just hang. Wireshark looked like I was sending a packet and just not getting any response.

While troubleshooting the issue I added an order by clause to the query trying to see if I could find a potentially problematic record, but then the query executed just fine.

I don't know why the order by clause fixed it and I don't care too much to find out. Hopefully this simple solution will work for others too!