[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1195
  • Last Modified:

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

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...
0
AutomateMyOffice
Asked:
AutomateMyOffice
2 Solutions
 
James MurrellProduct SpecialistCommented:
0
 
AutomateMyOfficeAuthor Commented:
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.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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:

http://msdn.microsoft.com/en-us/library/aa172445(SQL.80).aspx
0
 
dwe761Software EngineerCommented:
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 a.* FROM
(SELECT x,y,z FROM MyTable1 WHERE **Param1**) a
INNER JOIN
(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.
0
 
AutomateMyOfficeAuthor Commented:
Thanks!  Sorry for the delay in awarding points.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now