Hi Minty - regarding remote backends with an Access frontend, it is a bit of work to get right.
I generally categorize into three levels of "forgiveness" when it comes to performance and how much care needs to be given:
1 - Access (JET/ACE) backend: very forgiving
2 - SQL Server (or other ODBC) on a LAN: much less forgiving but still not terrible
3 - SQL Server (or other ODBC) over a WAN: very unforgiving, but when done right works very well.
We have a "3 second rule" - no form should take more than 3 seconds to open (and most of them are well within 2 seconds). We achieve this via remote backends as well, but it really needs to be worked at.
You might be interested in a rather in-depth article/whitepaper I wrote on the subject. It seems to be pretty well received:
https://dymeng.com/azure-series-05-database-performance/ It's written specifically for SQL Azure, but applies to any remote DB over ODBC (or any onsite LAN server as well, really).
As it happens, I'm re-presenting this topic at this year's Access DevCon in Vienna:
http://www.donkarl.com/devcon/agenda.htm (the presentation is build around that whitepaper).
Some things we'd expect to have to do (restricting records on form recordsets) are correct. Some not so much (leaving the record connected via usual DAO tables are fine). Mostly it boils down to how we're querying. If the queries are well constructed, the back and forth between the server are actually pretty good: queries that don't take in the proper considerations can take ages to run though. If you get bored, that article covers all of it in detail.
Cheers,