Parameter passing to SP

[Steve Burrows]

I have a SQL Server sp that requires 2 parameters. I wish to run the sp by
clicking a button on my user form in the Access Project. The two parameters
are all entered by the user in the form in two text boxes.

What I want is to know the VBA to call the sp and pass the parameters, so I
want something like:

param1 = me!text1
param2 = me!text2
docmd.execsp "sp_name", param1, param2

The execsp is my invention! Does anyone know the correct syntax and
instruction to do this?

Steve Burrows

 

[Malcolm Cook]

If you don't need a result/recordset, you can simply call the sp as though
it were a method on the access connection

access.CurrentProject.AccessConnection.p_thisIsMyProc(somearg, anotherarg)

 

[Walter Levine]

Your fix would solve one of my sql problems, but I cannot get it to work
when I try to code:
access.CurrentProject.AccessConnection.p_thisIsMyProc(somearg, anotherarg)
or
Application.CurrentProject.AccessConnection.p_thisIsMyProc(somearg,
anotherarg)
I get intellisense upto AccessConnection, then nothing
presumably, .p_thisIsMyProc is the name of a stored proc
Is it supposed to come up in the intellisense?
Thanks
Walter

 

[Malcolm Cook]

wont come up in intellisense, but it works

Your fix would solve one of my sql problems, but I cannot get it to work
when I try to code:
access.CurrentProject.AccessConnection.p_thisIsMyProc(somearg, anotherarg)
or
Application.CurrentProject.AccessConnection.p_thisIsMyProc(somearg,
anotherarg)
I get intellisense upto AccessConnection, then nothing
presumably, .p_thisIsMyProc is the name of a stored proc
Is it supposed to come up in the intellisense?
Thanks
Walter