Calling SQL Stored Proc from Access (with Parameters)

[Guest]

Hi

I am aware that one can run an SQL Server stored proc from Access and return
records and even pass in parameters to the stored proc to get the desired
result. At the moment I am doing this with a "Pass-through" query with
Hard-Coded parameters.

What I want to do however is have the parameter that is passed in to the
stored proc from access created dynamically from a form in Access.

eg.

exec spr_my_report [forms]![frmeomreports]![statementnumber]

instead of

exec spr_my_report 55


Or equivalent, any ideas?

Thanks,
Jesse

 

[[MVP] S.Clark]

In VBA, open the querydef, modify the SQL, save the query and then run it.

 

[Duane Hookom]

Modify the sql of the P-T with code like

Dim strSQL as String
strSQL = "exec spr_myReport " & Me.StatementNumber
CurrentDb.QueryDefs("qsptMyPT").SQL = strSQL

 

[Barry Jon]

Jesse,

not sure what your connection method is. I believe, using ADO, that you
could do something like this;

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnection = [relevant connection]
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spr_my_report"

cmd.Execute , Array([Parameter Value1], [Parameter Value2]...)

Let me know if I am missing the mark or if this works for you

Regards

Barry-Jon

 

[Guest]

Hi guys,

Thanks for your replies. How does one open up the code behind a query?

Jesse

Modify the sql of the P-T with code like

Dim strSQL as String
strSQL = "exec spr_myReport " & Me.StatementNumber
CurrentDb.QueryDefs("qsptMyPT").SQL = strSQL

--
Duane Hookom
MS Access MVP
--

Hi

I am aware that one can run an SQL Server stored proc from Access and
return
records and even pass in parameters to the stored proc to get the desired
result. At the moment I am doing this with a "Pass-through" query with
Hard-Coded parameters.

What I want to do however is have the parameter that is passed in to the
stored proc from access created dynamically from a form in Access.

eg.

exec spr_my_report [forms]![frmeomreports]![statementnumber]

instead of

exec spr_my_report 55


Or equivalent, any ideas?

Thanks,
Jesse

 

[Duane Hookom]

There is no code behind a query. A querydef has a SQL property that can be
modified using the code that I suggested.

--
Duane Hookom
MS Access MVP
--

Hi guys,

Thanks for your replies. How does one open up the code behind a query?

Jesse

Modify the sql of the P-T with code like

Dim strSQL as String
strSQL = "exec spr_myReport " & Me.StatementNumber
CurrentDb.QueryDefs("qsptMyPT").SQL = strSQL

--
Duane Hookom
MS Access MVP
--

Hi

I am aware that one can run an SQL Server stored proc from Access and
return
records and even pass in parameters to the stored proc to get the
desired
result. At the moment I am doing this with a "Pass-through" query with
Hard-Coded parameters.

What I want to do however is have the parameter that is passed in to
the
stored proc from access created dynamically from a form in Access.

eg.

exec spr_my_report [forms]![frmeomreports]![statementnumber]

instead of

exec spr_my_report 55


Or equivalent, any ideas?

Thanks,
Jesse

 

[Guest]

Thanks again for your responses but i'm not quite there yet.

This is the background. I have an access database and a SQL database. The
access database has a heap of linked SQL tables through 'Linked Table
Manager' to the SQL database. I have now created a Pass-Through query in
access which, as far as I understand, can run a piece of SQL directly on the
SQL database it links to. This works fine but I need to be able to change
the parameter that is in the Pass-Through query dynamically, ie. based on the
value of a form field that is open.

I have had good responses that sound like they will do what I want but I
need to know where to type the code that is given - considering I am using an
access database. I have tried Duane's code in the query but that is
obviously not the right spot for it. It looks like VB code, I don't know
where to put it though

Jesse

Jesse,

not sure what your connection method is. I believe, using ADO, that you
could do something like this;

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnection = [relevant connection]
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spr_my_report"

cmd.Execute , Array([Parameter Value1], [Parameter Value2]...)

Let me know if I am missing the mark or if this works for you

Regards

Barry-Jon

Hi

I am aware that one can run an SQL Server stored proc from Access and
return
records and even pass in parameters to the stored proc to get the desired
result. At the moment I am doing this with a "Pass-through" query with
Hard-Coded parameters.

What I want to do however is have the parameter that is passed in to the
stored proc from access created dynamically from a form in Access.

eg.

exec spr_my_report [forms]![frmeomreports]![statementnumber]

instead of

exec spr_my_report 55


Or equivalent, any ideas?

Thanks,
Jesse

 

[Duane Hookom]

You can put the code in the after update of your "form field". After the
text box (or combo box) is updated by the user, have the code change the SQL
property of the pass-through query. Assuming your pass-through is named
"qsptMyPT" and the text box/control is named "StatementNumber" then the code
in the after update event of the control "StatementNumber" would be:

Dim strSQL as String
strSQL = "exec spr_myReport " & Me.StatementNumber
CurrentDb.QueryDefs("qsptMyPT").SQL = strSQL

This would be in the form's code window.

--
Duane Hookom
MS Access MVP
--

Thanks again for your responses but i'm not quite there yet.

This is the background. I have an access database and a SQL database.
The
access database has a heap of linked SQL tables through 'Linked Table
Manager' to the SQL database. I have now created a Pass-Through query in
access which, as far as I understand, can run a piece of SQL directly on
the
SQL database it links to. This works fine but I need to be able to change
the parameter that is in the Pass-Through query dynamically, ie. based on
the
value of a form field that is open.

I have had good responses that sound like they will do what I want but I
need to know where to type the code that is given - considering I am using
an
access database. I have tried Duane's code in the query but that is
obviously not the right spot for it. It looks like VB code, I don't know
where to put it though

Jesse

Jesse,

not sure what your connection method is. I believe, using ADO, that you
could do something like this;

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnection = [relevant connection]
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spr_my_report"

cmd.Execute , Array([Parameter Value1], [Parameter Value2]...)

Let me know if I am missing the mark or if this works for you

Regards

Barry-Jon

Hi

I am aware that one can run an SQL Server stored proc from Access and
return
records and even pass in parameters to the stored proc to get the
desired
result. At the moment I am doing this with a "Pass-through" query with
Hard-Coded parameters.

What I want to do however is have the parameter that is passed in to
the
stored proc from access created dynamically from a form in Access.

eg.

exec spr_my_report [forms]![frmeomreports]![statementnumber]

instead of

exec spr_my_report 55


Or equivalent, any ideas?

Thanks,
Jesse

 

[Malcolm Cook]

Here's an approach to creating a VB function TEST_CALL_SQL_PROC to wrap
around a SQL Stored proc named p_TEST_CALL_SQL_PROC that takes one
parameter.

Note that it declares a static command so it only has to be prepared once;
subsequent calls will use the already prepared command.

Public Function TEST_CALL_SQL_PROC(SomeParam As String) As ADODB.Recordset
Static cmdTEST_CALL_SQL_PROC As ADODB.Command
If cmdTEST_CALL_SQL_PROC Is Nothing Then
Set cmdTEST_CALL_SQL_PROC = New ADODB.Command
With cmdTEST_CALL_SQL_PROC
.ActiveConnection = CurrentProject.AccessConnection
.CommandType = adCmdStoredProc
.prepared = True 'NOTE: this causes the .Parameters to 'materialize'
thanks to some help from ADO.
.CommandText = "p_TEST_CALL_SQL_PROC "
End With
End If
With cmdTEST_CALL_SQL_PROC
With .Parameters
![@SomeParam] = SomeParam
End With
Set TEST_CALL_SQL_PROC = .Execute
End With
End Function

 

[Guest]

Thanks everyone. I ended up doing as suggested in Duane's last post and it's
all good.

Excellent.

Here's an approach to creating a VB function TEST_CALL_SQL_PROC to wrap
around a SQL Stored proc named p_TEST_CALL_SQL_PROC that takes one
parameter.

Note that it declares a static command so it only has to be prepared once;
subsequent calls will use the already prepared command.

Public Function TEST_CALL_SQL_PROC(SomeParam As String) As ADODB.Recordset
Static cmdTEST_CALL_SQL_PROC As ADODB.Command
If cmdTEST_CALL_SQL_PROC Is Nothing Then
Set cmdTEST_CALL_SQL_PROC = New ADODB.Command
With cmdTEST_CALL_SQL_PROC
.ActiveConnection = CurrentProject.AccessConnection
.CommandType = adCmdStoredProc
.prepared = True 'NOTE: this causes the .Parameters to 'materialize'
thanks to some help from ADO.
.CommandText = "p_TEST_CALL_SQL_PROC "
End With
End If
With cmdTEST_CALL_SQL_PROC
With .Parameters
![@SomeParam] = SomeParam
End With
Set TEST_CALL_SQL_PROC = .Execute
End With
End Function

Hi

I am aware that one can run an SQL Server stored proc from Access and return
records and even pass in parameters to the stored proc to get the desired
result. At the moment I am doing this with a "Pass-through" query with
Hard-Coded parameters.

What I want to do however is have the parameter that is passed in to the
stored proc from access created dynamically from a form in Access.

eg.

exec spr_my_report [forms]![frmeomreports]![statementnumber]

instead of

exec spr_my_report 55


Or equivalent, any ideas?

Thanks,
Jesse