How do I pass parameters to nested queries in SQL Server from Microsoft Access?

Posted on 2009-02-22
Last Modified: 2012-05-06
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...
Question by:AutomateMyOffice
    LVL 31

    Expert Comment

    by:James Murrell

    Author Comment

    I also recommend the SQL Server Migration Assistant.  It is a powerful tool that works far better than the built-in Access Upsizing Wizard.

    SSMA manages a great deal of the task, but migrating parameter queries to SQL Server -- queries that require their values to be passed from Acces -- has always required special care and hand-tooling.  They are tedious to code, compared with the ease with which they can be set up in the Access query builder.  And now I am faced with the added challenge of passing values to NESTED queries -- and that is the area for which I am requesting specific help.
    LVL 57

    Assisted Solution

    by:Raja Jegan R
    If you are able to execute a procedure from Access, try framing the input query in the string and execute it with sp_executesql

    all declaration;
    set @sqlstring = N'select * from tablea where tablea.col1 = ( select max(col2) from table2 where table1.col1 = table2.col2 and table2.col3 = ' + ? + N')'
    exec sp_executesql, @paramdefinition,

    Checkout Substituting Parameter strings in the below link for your reference:
    LVL 10

    Accepted Solution

    There are some things that Access does very well and we get spoiled by the ease of some of it's operations.  Then when we go to a larger platform, we realize how nice we had it.  Obviously, you had good reasons for going to a larger platform so this will be one of the prices you'll pay.  Unfortunately, I don't know if there is a one-technique-fits-all solution to what you're asking.  It depends on the complexity of what you're doing, the size of the tables, where the tables reside, and the amount of time you have to get the development done, etc.

    I use a combination of techniques, but generally, I use more pass-through queries unless it is a more complex operation in which I need to have nested stored procs that generate temp tables or need to implement potential rollbacks, etc.  Pass-through queries allow the flexibility to easily insert parameters into a SQL statement on the fly and I find code easier to debug on the client side.

    If your parameters are only single values from a form, it is simpler to send all of those parameters in a passthrough query which only calls a stored proc.

    I have a table on the client side containing a lot of SQL strings with some unique string where parameters will replace that unique string.  Then when I need to use that SQL string:
    1) I grab the SQL from that table based on the name of the report or operation
    2) REPLACE() all unique strings with the necessary parameters,
    3) Place that Sql STRING into a generic pass-through query
    4) and run it.

    Remember that you can also join pass-thru queries on the client side or join a pass-through query with an Access query.  However, you just have to watch that you are using the best technique for optimum performance.  Joining two pass-through queries on the client side that had returned large recordsets is not a good idea. You'd be much better off in this case either writing a stored proc or generating a complex query that you send in a pass-through query.

    (SELECT x,y,z FROM MyTable1 WHERE **Param1**) a
    (SELECT d,e,f FROM MyTable2 WHERE **Param2**) b
    on a.x = b.d

    Another idea is if you've got small lookup tables on your client side that you use in your queries, write a function to convert a lookup table of values into a comma-delimited list.  Then you can easily call that function to paste a where clause "WHERE MyFld IN (a,b,c,d) " into your SQL statement instead of using your lookup table.

    Anyway, I hope some of these ramblings help.

    Author Closing Comment

    Thanks!  Sorry for the delay in awarding points.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now