Adam, to answer the direct question:
The best back-end for an Access front-end is the one that will do the job you want with the least trouble and cost, and that has sufficient capacity, longevity, and durability. My viewpoint on this is that there is no unique and eternally correct answer to the question as asked, because it has no context.
For my monster for the Navy, a native Access back-end was fine. But for the BUMED project that I sometimes had to step in for a consult, they were using SQL Server because they had a capacity problem and needed a specific type of security.
You know from other threads that I believe in having multiple tools in my toolkit. The kind of back end you need is the one that works in your circumstance. If circumstances change, the answer will change. Sometimes you need a hammer. Sometimes you need a screwdriver. Sometimes you need a post-hole auger, though the Navy uses that more often for anatomical rearrangement if they think you need reaming out.
Can you name one of a programmer's greatest strengths? That was actually rhetorical: Flexibility under changing conditions. Applications are living things as long as people use them and life goes on. Because requirements change. Regulations change. User mix changes. So why should the answer to that "best back end" question not change?
For my biggest mainframe DB, which was the U.S. Naval Reserve's Personnel Management DB (and I can't talk about inner details much due to NDAs), the first three or four years were crazy because we outgrew two different machines. We beat one machine to death. No, not kidding - a RAID-1, or mirrored, drive literally fell apart because it was a "hot spot" of usage. The mirror failed and demoted itself to a single drive, which failed fifteen minutes later. Took us three weeks to recover from that hardware failure.
We eventually went with ORACLE on OpenVMS because that allowed us to distribute the DB over multiple disks and to monitor individual disk I/O loads for hot spots - and to migrate data to spread the load. Since ORACLE allows ODBC connections, we COULD have used Access as a front-end for that, too - but there was a third-party OpenVMS product we used because the project pre-dated the wide-spread use of Access and not everyone had a "smart" terminal anyway. So we used a dumb-terminal program for the early stuff and just never converted to the newer technology. Which was why after over 30 years, they finally stood down the old system and stood up a fully web-enabled system. But that was OK because I had a good run with it and the Navy was able to salvage the back end design for the new front-end. So in the end, the taxpayers got some benefits from a well-handled project. (Don't you DARE think that EVERY military project is a total boon-doggle!)
But back to topic - any answer you try to nail down is wrong the moment you drive in the nail.