Yes you can but you'll have to pass it into a function or subroutine. What i do is pass it into the function that calls my procedure.
Public Function RunMyStoreProc(strEnd as string)
Dim adComd as Adodb.Command
dim conn as adodb.Connection
dim adParam as adodb.Parameter
set conn = New Adodb.Connection
set adComd = New adodb.Command
set adParam = New Adodb.Parameter
conn.CursorLocation = adUseClient
conn.ConnectionString = myPath
conn.Open
adComd.CommandType = adCmdStoredProc
adComd.CommandText = "MyStoredProcedureName"
Set adParam = adComd.CreateParameter("TargetDate", adVarChar, adParamInput, 12, strEnd)
adComd.Parameters.Append adParam
adComd.ActiveConnection = conn
adComd.Execute
set conn = nothing
set adComd = nothing
set adParam = nothing
End Function
When creating the parameter you need to name the variable as it is in the stored proc., data type, whether it is an input or an output variable, (if its a string)the max length of the variable in the stored proc and then the value of the parameter (in this case strEnd which was an argument in the function).
If the stored proc has more than one variable, you need to append each in the order that they declared in the stored proc, one at a time (you can ease this by copying and pasting the lines of code and change those things that need changing)
The stored proc does not need to just INsert, Update or Delete. A command object can return a recordset, this allows you to set the connectiontimeout and commandtimeout properties.
It was a long one, but it should work fine for you.
BB
;
