Executing stored procedures

[Andy Williams]

Here's a chunk of code in the OnOpen event of a form:

Dim cnn1 As New ADODB.Connection
Dim cmd1 As New ADODB.Command
Dim prmUser As New ADODB.Parameter
Dim rst1 As New ADODB.Recordset

Dim strUser As String

strUser = fOSUserName
cmd1.ActiveConnection = CurrentProject.Connection

With cmd1
Set prmUser = .CreateParameter("prmNTUserName", adChar, adParamInput,
Len(strUser), strUser)
.Parameters.Append prmUser
.CommandType = adCmdStoredProc
.CommandText = "spUsers"
Set rst1 = .Execute
End With

Me!txtRequestor = rst1.Fields("DisplayName")
Me!txtDepartment = rst1.Fields("Department")

----

I'm wondering if there is a better way to execute a stored procedure. I'm
sure there are alternatives, but this is just the method I've always used.
It works fine, but gets really bloated if you have to pass more than one or
two parameters.

Any suggestions?

 

[Vadim Rapp]

AW> I'm wondering if there is a better way to execute
AW> a
AW> stored procedure.


Dim Rst1 as new recordset
with rst1
.open "spUsers('" & strUser & "')",currentproject.connection
txtRequestor = !DisplayName
txtDepartment = !Department
.close
end with


Vadim

 

[Andy Williams]

Ahhh... Thank you sir, works great!