I've recently done this for a project I was working on, all you need is a reference adding to ActiveX Data Objects

Public Function ExecuteSP(strStoredProcedure As String) As Long

Dim objCmd As ADODB.Command

Dim lngRecords As Long

Set objCmd = New ADODB.Command

objCmd.ActiveConnection = "PROVIDER=SQLOLEDB;DRIVER={SQL Server};SERVER=" ' Complete your connection string here

objCmd.CommandType = adCmdStoredProc

objCmd.CommandText = strStoredProcedure

Call objCmd.Execute(lngRecords)

ExecuteSP = lngRecords

Set objCmd = Nothing

End Function

This is a generic function for calling SPs without parameters that may return an affected record count, parameters could be added as follows

objCmd.Parameters.Append objCmd.CreateParameter("@ParamName", adInteger, adParamInput, value:=1)