I don'g know why you just can't put you SQL directly into the excel code?
the SQL that works in Access.

The OP was talking about a stored procedure (SQL Server), not MS Access.

To try to give you some idea why the OP should not use dynamic SQL, from my
point of view, has to do with accessibility and ease of maintenance. Let's
say you have a user who has a support issue, and as a result of that, you
must make a change to the SQL. The change being made must be available to
everybody.

Using a stored procedure, this is very easy to deal with -- simply edit the
stored proc and execute the change. Done..end of support issue. Using
dynamic SQL, on the other hand, would require a redeployment of the client
piece of the solution -- not something that I want to deal with unless
absolutely necessary. In the real world this might manifest itself like the
following:

1. Get a support call from a user that the recordset is not returning the
correct results.
2. The developer opens the client app (let's say it is an Excel
workbook/template with macros), points the environment to production and
replicates the results.
3. The developer modifies the code, then must re-deploy the application.
4. The template is already opened by some users, so you send out an email
informing users to be sure to save their changes, close the template,
re-open (to get the latest copy), then continue.
5. Note: in the real world, users may not have the time to perform step 4
(think the financial industry where a few minutes is a small eternity -- say
this happens during a volatile time of day and they don't have time until
after the market closes Or, users are right in the middle of some work and
cannot just simply close out of everything without a serious disruption).
6. Other users continue to use your application, but they, too, get the
wrong recordset, and issue support requests.
7. The developer repeats step 4 with as many people as needed.
8. The support of this issue, which would have been 15 minutes for a
SQL-centric distribution, has turned into a 1+ hour issue, and is still not
resolved because some users were away from their desks when the step 4 email
was sent..

With a SQL-centric solution, the support issue might manifest itself like
the following:
1. Get a support call from a user that the recordset is not returning the
correct results.
2. The developer goes directly to the stored procedure, executes it and sees
the problem, then fixes it.
3. While still on the phone with the user, the developer tells the user to
click the button again. The user is happy the results are correct now.
Everybody else will get the correct results too with no changes to their
environment.

There are other reasons to avoid using dynamic SQL, relating to permissions,
SQL injection, query plans/caching, and many other things. See:

The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html


--
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility
Free and Pro versions

"joel" wrote in message
...
I don'g know why you just can't put you SQL directly into the excel code?
the SQL that works in Access. In Excel I usually get my SQL statements
working by using the SQL editor and entering one parameter at a time until
it
works.