Accessing Stored Procedure via VBA

  • Thread starter Thread starter Patrick Pohlmann
  • Start date Start date
P

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
 
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)
 
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
 
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)
 
Patrick Pohlmann said:
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
Click to expand...
 
Patrick
If you want to use the stored procedure as the recordsource for the form....
simply put the sp name in the recordsource property, then in the
InputParameters property put @one = 'Value 1', @two = 'Value 2'

IF this form is a subform and you want to change the data in the subform
when you click on a row in another subform....
in the on current even of form 1, put....
something like...

Forms![MyMainForm].form2.form.inputparameters = "@one = " & me.tbOne & ",
@two = " & me.tbTwo

I think you will be plesantly surprised with the speed this provides.
hth,
...bob
 
Back
Top