It's my view that Access itself does not really lend itself for a n-tiered architecture. I'm not saying it's not possible; just not cost-effective and requires swimming upward.
Mind, Access does admirably in a 2-tiered architecture (e.g. client-server architecture). To take the next step and go n-tiered requires that we write a library between the server and client. The whole point of building n-tiered architecture is to enable consumers to change backends and work with more than one clients. So, if we had requirement to provide same application in a web browser, in a rich client, or even two different clients (e.g. one client optimized for Windows, other for *nix perhaps) and/or we wanted to be able to deploy this application to any environment regardless of whether they're running SQL Server, Oracle, PostgreSQL or MySQL.... that's when we really need a n-tiered architecture. I know you've mentioned you want Access FE - Access BE but that'd argue against the need for n-tiered architecture precisely because of lack of need to deploy application on different platforms.
As lagbolt pointed out, it is certainly possible to build an Access application as a client in a n-tiered architecture if your middle tier can be consumed by Access (which implies that the library must be a COM DLL) or perhaps exposing objects as ODBC or OLEDB objects thus enabling you to keep as much as logic you can in the library. The latter would certainly increase the complexity and requirement, perhaps too much to justify that the former method and thus undergoing the benefits of bound forms and using VBA to drive everything may be easier to implement. But if we choose to forsake bound forms, the next question then become "Why even bother with Access? Might as well go and do it in Visual Studio."
Indeed, the whole strength of Access rests in the bound forms and thus RAD aspect. N-tiered architecture does not mesh with RAD very well, hence my position that Access wouldn't really make for a good n-tiered client.
The increase of complexity from a client-server architecture to n-tiered architecture is quite big, and we also need to consider the case of time to market. Many times, Access applications are made because there is a business requirement that should be met yesterday. Indeed, several Access applications out there were born in response to IT's hand waving, saying it'd be months before they wrote out a "real application" but they can't wait months. Then there's also the additional problem of changing requirement. A feature request is easier done in a client-server architecture but quite expensive in a n-tiered architecture.
There are times and places for a n-tiered architecture and when the requirement is justified, they are excellent solutions. But if it's not really needed, it's a good way to burn money and time.
It boils down to this: Just because we can, doesn't mean we should.
