Для этих целей используется объект Command и Parameter
вот есть пример, можно обойтись без Connection


dim Cn as adodb.Connection
Dim Rs As New ADODB.Recordset
Dim Comm As New ADODB.Command
Dim par As New ADODB.Parameter
Dim ConStr As String

ConStr = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=DateBaseName;" & _
"Data Source=ServerName"

With Cn
.ConnectionString = ConStr
.Open
End With
On Error GoTo ErrLabel
With Comm
.ActiveConnection = Cn
.CommandText = "sp_FindData"
.CommandType = adCmdStoredProc
.Prepared = True
.Parameters.Refresh
.Parameters(1) = StreetName
.Parameters(2) = HomeName
End With
Set par = Comm.CreateParameter("Street", adChar, _
adParamInput, Len(StreetName), StreetName)
Comm.Parameters.Append par
Set par = Comm.CreateParameter("Home", adChar, _
adParamInput, Len(HomeName), HomeName)
Comm.Parameters.Append par

Set Rs = Comm.Execute()

Увидела неточность в своем примере:
on error goto... нужно перенести перед With cn