Run a SQL Stored Procedure..

[imtheodore]

Is there a way to run a Stored Procedure in SQL from Access?
I have a rather long query that takes too long to run locally and a stored procedure would work for this, but I cannot figure out how to run it.
I would like it to run when I open the Access db.
The project I'm working on is using a SQL back end since the data is also used on our internet pages..
I will have to pass a date to the procedure and execute it.
Is this even possible?

Dave

 

[KenHigg]

I think you may be able to do this with a pass-through query - ?

 

[namliam]

I think you may be able to do this with a pass-through query - ?

Yes you are right... but make sure to "not expect" records back...
Makes it an action query... this is an option in the Pass through query

 

[imtheodore]

Yes you are right... but make sure to "not expect" records back...
Makes it an action query... this is an option in the Pass through query

Thats perfect, I do not need records back, another query will do that.
Do you have an example on how to set up the query?

 

[namliam]

No not on me at the moment...

Just create a new query...
Change it to passthrought-action query...
Exec StoredProcedure

That should do it...

 

[imtheodore]

No not on me at the moment...

Just create a new query...
Change it to passthrought-action query...
Exec StoredProcedure

That should do it...

thanks You, that did work. Now I have to find a way to pass a date through it...

 

[namliam]

exec storedprocedure(parameter1, parameter2)

 

[imtheodore]

exec storedprocedure(parameter1, parameter2)

I need to pass a date from a listbox. Is that possible?
So when the user chooses a date, I can run the procedure for that date.

 

[namliam]

Sure anything is possible.

Just adjust your "sql" as per necesary...

Currentdb.querydefs ("queryname").sql will allow you to adjust your "sql" ...

Lookup querydefs in the help, for more details.