How do I pass parameters to nested queries in SQL Server from Microsoft Access?
Posted on 2009-02-22
Now that I am moving more toward SQL Server -- still using a Microsoft Access front end -- I am converting my Access queries to views, stored procedures and pass-through queries that execute on the server side. When I need to provide parameters, I construct the SQL or T-SQL in VBA, incorporating parameter values as needed, and submit it to the server.
This works fine when the queries require parameters only at the top level, but I often employ nested queries in Microsoft Access, where parameters for the underlying queries easily take their values directly from controls on screen forms. How do I accommodate the parametric requirements for underlying nested queries in my back-end SQL Server, whose values need to be taken from the Access form controls on the front end? Do I need to run the nested queries first, somehow creating temporary tables that are subsequently referenced by the higher-level queries? Maybe there is an easy and obvious solution, but somehow I am missing it...