Using a SQL Stored Procedure in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Back
Top