Accessing Stored Procedure via VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Patrick Pohlmann - 04 Feb 2010 15:41 GMT

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

Reply to this Message

Sylvain Lafontaine - 04 Feb 2010 16:13 GMT

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.

Signature

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,
>
[quoted text clipped - 18 lines]
>
> Patrick

Reply to this Message

Patrick Pohlmann - 05 Feb 2010 09:56 GMT

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

> You must either build a query string using the EXEC statement (untested):
>
[quoted text clipped - 35 lines]
>>
>> Patrick

Reply to this Message

Sylvain Lafontaine - 05 Feb 2010 17:01 GMT

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 »

Signature

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,
>
[quoted text clipped - 46 lines]
>>>
>>> Patrick

Reply to this Message

Debra - 08 Feb 2010 19:58 GMT

> Hi,
>
[quoted text clipped - 18 lines]
>
> Patrick

Reply to this Message

Bob McClellan - 10 Feb 2010 02:25 GMT

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

> Hi,
>
[quoted text clipped - 18 lines]
>
> Patrick

Reply to this Message