Accessing Stored Procedure via VBA

[Patrick Pohlmann]

Hi,

I am trying to work with Stored presucures insted of buliding querys within
vba. I am working with an access .adp (Access 2007) and SQL Server 2005

There are two quesitions I would like to know:

1. How to use a stored procedure with parameters as recordsource for an
access form.
2. How to open a stored procedure via vba to view the results

For example my Prozedure is called: proc_myproc and it need two parameters
like @one und @two.

I would be very happy if someone could tell me how to do this.

Thanks a lot.

Regards

Patrick

 

[Sylvain Lafontaine]

You must either build a query string using the EXEC statement (untested):

Dim sql as string
sql = "EXEC MySP 1, 2, 'Sylvain' "
Form.RecordSource = sql

Or you can use the InputParameters property to define each parameters. For
the EXEC statement, you don't need to refresh or requery the form after
changing the record source as this is done automatically. For complex
cases, using the UniqueTable and the ResyncCommand will help you.

This has been explained many times in the past, so you can search this
newsgroup for these keywords. I will try to soon write an article on this
on my blog that I've started two months ago; however, I'm already late for
many weeks on this because I've had to learn things like
Photoshop/GIMP/Paint.Net first.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Patrick Pohlmann]

Hi Sylvain,

thank you very much. Look quite easy for me. Thanks.

Can you help me with me secound question, too? How can I open a SP as a view
with parameters via VBA. So that the user can see the results?

Thanks again.

Patrick

 

[Sylvain Lafontaine]

Second question: I have no idea: I always use forms to open even the
simplest SP and provide the required parameters. I remember seen some posts
on this topic but I never give them any real attention.

You can use the Currentproject.Connection.Execute to call a stored procedure
with parameters by using the EXEC call as explained earlier but I don't know
if this can display the result of a query to the user. Search Google for
« currentproject.connection.execute stored procedure EXEC »

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Debra]

Hi,

I am trying to work with Stored presucures insted of buliding querys
within vba. I am working with an access .adp (Access 2007) and SQL Server
2005

There are two quesitions I would like to know:

1. How to use a stored procedure with parameters as recordsource for an
access form.
2. How to open a stored procedure via vba to view the results

For example my Prozedure is called: proc_myproc and it need two parameters
like @one und @two.

I would be very happy if someone could tell me how to do this.

Thanks a lot.

Regards

Patrick