Using a SQL Stored Procedure in Access

[Guest]

Hi,
Currently we have number of queries in an Access mdb that we run to update a
couple of SQL tables. The problem is that all the queries must be run and in
a certian order or the updating is not correct. The process is subject to
human error. My thinking was to combine all the queries into a SQL Stored
Procedure, still using one of the tables in the mdb for the criteria. I
posted on the SQL forum and they gave me a way that involves turning on a
security risked feature that our Sys Admin is not willing to do. So, now I'm
here asking you all if you have any ideas. Ideally I would like the sp to
reside on the SQL server and to have access feed it the criteria. The problem
is that I can only find info on passing one bit of info...not a whole table.
The Access table used for the criteria changes...sometimes only a couple of
records other times 20 or 30. The criteria table needs to stay in Access as
that is the easiest way for the end-users to use it.

Long post, sorry about that....I'm starting to get a bit frustrated.

Any ideas and help is greatly appreciated,
Mary

 

[Jeff Boyce]

If you are saying that you need to run, in the exact following order, Q1,
Q7, Q2, Q6, Q5, Q3, and Q4, use a macro.

Open a new macro in design view, add each query in the order you need.

Now when you need to do the update, do the macro.

(or, you could build a function that calls each of the queries in the
correct order)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks Jeff,
That is a really good solution. Thanks for your help.