Access 2007 is supposedly easier for the basic user and tough for the power user. My applications built for 2002/2003 work without issue on 2007, the only issues being the default disabling of code, which once worked through aren't a continued hindrance, and the changes in screen real estate, which harm the form presentation a bit.
As for ADP vs MDB (with linked tables) vs MDB (with stored procs) , the last last option seems the best option, as it provides the best upgrade path for the client and the best security. In a recently-delivered Access with SQL Server (stored procs) application, performance was very good - an ADP was not an option as it was very slow - and since I separated the application forms from the data via a separate class, the app would be fairly easy to migrate to a VB.NET solution using either the web or a fat client. As for security, having no linked tables meant that users couldn't harm the data.