Sub Test()
Dim conCon As New ADODB.Connection
Dim cmdCom As New ADODB.Command
Dim rstRec As New ADODB.Recordset

If  1  =  1  Then
  conCon.Provider = "sqloledb"
  conCon.Properties("Data Source") = "Имя сервера"
  conCon.Properties("Initial Catalog") = "База данных"
  conCon.Properties("Integrated Security") = "SSPI"
  conCon.Open
 Else
  Set conCon = CurrentProject.Connection
  Debug.Print conCon.Provider
End If

cmdCom.ActiveConnection = conCon
cmdCom.CommandText = "TempTest"
cmdCom.CommandType = adCmdStoredProc
'cmdCom.ActiveConnection.CursorLocation = adUseServer
cmdCom.ActiveConnection.CursorLocation = adUseClient
cmdCom.Parameters.Append cmdCom.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue)

conCon.Execute "CREATE PROCEDURE TempTest AS select 1 RETURN "

Set rstRec = cmdCom.Execute

MsgBox IsEmpty(cmdCom.Parameters("@RETURN_VALUE"))

conCon.Execute "drop procedure TempTest"
End Sub