Running a stored procedure from an Access project

[kiloez]

What's the best way to run a stored procedure from code in a Access project?
I'm currently passing a string to the rst.open method, using something like
this:

Dim strSSN As String
Dim rst As ADODB.Recordset
Dim strSproc As String

strSSN = InputBox("Please enter SSN to search", "Client Search")

strSproc = "execute dbo.spGetClientBySSN " & "'" & strSSN & "'"

Set rst = New ADODB.Recordset

rst.Open strSproc, CurrentProject.Connection


It works just fine, but I'm wondering if there is a better way.

 

[Tom van Stiphout]

On Mon, 8 Sep 2008 11:35:01 -0700, kiloez

I think it is better to create an ADODB.Command object, and add
Parameters to it representing the sproc arguments. That gives you much
more flexibility, for example if you have an Output parameter.

Also, don't repeatedly create these objects in every function that
needs a recordset, but consolidate common code (e.g.
GetReadonlyRecordset) in a (class) module.

-Tom.
Microsoft Access MVP