We've been doing that very thing for about 20 years, starting with Access 2.0 Beta in 1994, and SQL Server 4.21. Although we do use other strategies from time to time, 95% of the time we use linked SQL Server tables in Access using ODBC. More recently we've been working with DNSless linked tables on some projects, but it still is ODBC. We've found that to be the fastest and most effective way to develop custom applications (by a factor of 2 to 4 or more depending on the kind of application). The strategy we use is to get it developed first and then test for performance issues afterward. We seldom find more than 5% of the queries or reports that need tuning. In those cases, we may resort to a pass-through query, or in some cases actually run a stored procedure from Access. And nearly all of the forms and reports we create are bound to a data source - I've only done a handful of unbound forms in those 20 years.

One option you didn't mention was that of using an ADP. That connects directly to SQL Server, but has the complication of having to do everything in stored procedures or views. We find that environment takes significantly longer to develop in. In addition, while it is available in 2007 and 2010, it has had no significant new functionality added since Access 2002, and it has been deprecated from Access 2013.

I've looked through a number of resources, and don't really find a good tutorial on getting started with Access front-ends to a SQL Server back-end, but http://www.mssqltips.com/sql-server-tip-category/57/microsoft-access-integration/ has several tips associated with how to go about it. There are some books on the subject - but my favorite one is now out of print. So post questions here or in one of the other Access forums and you should get on OK.

Wendell

Colorado, USA