Using SQL Server specific code in Access linked to SQL Server database

I have an access file that is linked (through an ODBC connection) to a SQL Serv开发者_StackOverflow社区er 2008 database. I am trying to write some reports against this database. However, Access chokes when I write the select query of the report with SQL syntax specific to SQL Server that doesn't exist in access. Shouldn't this work, since it's the SQL Server engine running the queries and just sending the data back to Access to display? Is there any way to get this to work?

Need this to work on any combination of Access 2007 and 2010, and SQL Server 2005 and 2008.

Edit

Note: I cannot create a SQL Server stored procedure or function, or otherwise modify the original (SQL Server) schema in any way.

---

Found the answer: I need to use a "pass-through query".