ODBC allows an MS Access developer to use all of the skills learned using MDB's on SQL Server . That does not mean SQL Server specifics can be skipped over -- both Access & SQL Server constructs need to be mastered. However, unlike ADP's, an MS Access database (MDB) with ODBC links to SQL Server (either 2000 or 2005) will enjoy all the strengths of DAO native to MS Access.
A simple example is the recordset behind an MS Access form or report. Try using ADO recordsets anywhere in an MS Access form or report. Or try to manipulate user level permissions with ADO DDL.
That said, there are caveats. Any DAO recordset that uses a SQL Server table must include the dbSeeChanges option. Also, some queries are to complex for ODBC to handle in translation for SQL Server, so a Pass-Thru Query must be used. These 2 caveats constitute the bulk of issues encountered in an MDB with ODBC links to SQL Server.
What's left after "the bulk of issues?" Maybe 1%. This web chapter is dedicated to that 1%.