EdA ROC (9/22/2012)
A little background about me and my situation. We have a 3rd party ERP system, SQL Server based. My primary job is to use Access to develop reports.
I'm no Access expert, but I think it is true to say that pass-through queries have to be a single SELECT statement. If you are able to create objects in the SQL Server, you could consider creating a stored procedure there. Access can call a stored procedure, and SQL Server stored procedures can contain multiple statements and use temporary objects like table variables and temporary tables.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi