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)


"Patrick Pohlmann" <(E-Mail Removed)> wrote in message
news:E7A11F31-DF07-40F9-A6FA-(E-Mail Removed)...
> 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
>
>
>

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" <(E-Mail Removed)> schrieb im Newsbeitrag
news:#(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:E7A11F31-DF07-40F9-A6FA-(E-Mail Removed)...
>> 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
>>
>>
>>
>
>
>

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" <(E-Mail Removed)> wrote in message
news:0693BFE3-DCE0-4C36-A600-(E-Mail Removed)...
> 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" <(E-Mail Removed)> schrieb im
> Newsbeitrag news:#(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:E7A11F31-DF07-40F9-A6FA-(E-Mail Removed)...
>>> 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
>>>
>>>
>>>
>>
>>
>>

"Patrick Pohlmann" <(E-Mail Removed)> wrote in message
news:E7A11F31-DF07-40F9-A6FA-(E-Mail Removed)...
> 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
>
>
>

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


"Patrick Pohlmann" <(E-Mail Removed)> wrote in message
news:E7A11F31-DF07-40F9-A6FA-(E-Mail Removed)...
> 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
>
>
>