Dim StrSproc As String

    cnnStr = "Provider=SQLOLEDB;Data Source=DBSource;" & "Initial Catalog=CurrentDb;" & _
             "Integrated Security=SSPI;"
    With cnn
       .CommandTimeout = 900
       .ConnectionString = cnnStr
       .Open
    End With
    With cmd
       .ActiveConnection = cnn
       .CommandType = adCmdStoredProc
       .CommandText = "[StoredProcedureName]"
       .Parameters.Append.CreateParameter("@parameter1", adInteger, adParamInput,, 0)
       .Parameters.Append.CreateParameter("@parameter2", adInteger, adParamInput,, 0)
       .Parameters.Append.CreateParameter("@parameter2", adInteger, adParamInput,, 0)
    End With
    With Rs
       .CursorType = adOpenStatic
       .CursorLocation = adUseClient
       .LockType = adLockOptimistic
       .Open cmd
    End With
    Application.StatusBar = "Running stored procedure..."
    Set rst = cmd.Execute
End Function

A:

This is my preferred approach:
Function Sproc()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim ConnectionString As String
    Dim StrSproc As String

    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=DBSource;" & _
                  "Initial Catalog=CurrentDb;" & _
                  "Integrated Security=SSPI;"

    'Opens connection to the database
    On Error GoTo SQL_ConnectionError
    cnn.Open ConnectionString
    On Error GoTo 0
    'Timeout error in seconds for executing the entire query; this will run for 15 minutes before VBA timesout, but your database might timeout before this value
    cnn.CommandTimeout = 900

    Set rst = New ADODB.Connection
    StrSproc = "set nocount on; "
    StrSproc = StrSproc & "EXEC StoredProcedure " & _
                        "@parameter1 = 0," & _
                        "@
Dim StrSproc As String 

     cnnStr = " Provider = SQLOLEDB;Data Source = DBSource; " & " Initial Catalog = CurrentDb; " & _ 
              " Integrated Security = SSPI; " 
     With cnn 
        .CommandTimeout = 900 
        .ConnectionString = cnnStr 
        .Open 
     End With 
     With cmd 
        .ActiveConnection = cnn 
        .CommandType = adCmdStoredProc 
        .CommandText = " [ StoredProcedureName ] " 
        .Parameters. Append. CreateParameter("@parameter1 ", adInteger, adParamInput, , 0) 
        .Parameters. Append. CreateParameter("@parameter2 ", adInteger, adParamInput, , 0) 
        .Parameters. Append. CreateParameter("@parameter2 ", adInteger, adParamInput, , 0) 
     End With 
     With Rs 
        .CursorType = adOpenStatic 
        .CursorLocation = adUseClient 
        .LockType = adLockOptimistic 
        .Open cmd 
     End With 
     lotion. StatusBar = " run stored procedure... " 
     Set rst = cmd. perform 
 conclusion Function 

 A: 

 This is my preferred access: 
 Function Sproc () 
     Dim cnn As ADODB.Connection 
     Dim rst As ADODB.Recordset 
     Dim cmd As ADODB.Command 
     Dim ConnectionString As String 
     Dim StrSproc As String 

     Set cnn = New ADODB.Connection 
     cnn. ConnectionString = " Provider = SQLOLEDB;Data Source = DBSource; " & _ 
                   " Initial Catalog = CurrentDb; " & _ 
                   " Integrated Security = SSPI; " 

    ' Opens connection to the database 
     On Error GoTo SQL_ConnectionError 
     cnn. Open ConnectionString 
     On Error GoTo 0 
    ' Timeout mistake in seconds for executing the integral query; this will run for 15 minutes before VBA timesout, but your database might timeout before this value 
     cnn. CommandTimeout = 900 

     Set rst = New ADODB.Connection 
     StrSproc = " jell nocount on; " 
     StrSproc = StrSproc & " EXEC StoredProcedure " & _ 
                         " @parameter1 = 0, " & _ 
                         " @
Dim StrSproc As String

    cknStr = "Provider=SQLOLEDB;Vata Sohrce=DBSojrce;" & "Initial Catalog=CurrentVb;" & _
             "Integrated Secjrity=SSPI;"
    With cnn
       .CommehdTimeomc = 900
       .EoinectionString = cnnStr
       .Open
    Eng Dich
    With cmd
       .ActiveConnectiog = cnn
       .CommandTypg = adBmqStodvdKroc
       .CommandText = "[StoredProdedureNeme]"
       .Paramrters.Append.CreateParameter("@oaraleter1", adInteger, adOaramInput,, 0)
       .Parameters.Xppend.Creaue'arameter("@patameter2", adInteger, adParamInput,, 0)
       .Parameterw.Appejg.CreateParaneter("@iarameter2", adIkneger, agParamImput,, 0)
    End Wlth
    Wuth Rs
       .CursorTy'e = adOpenStatic
       .CursorNoeation = adUseClient
       .LowkTy[e = xeLozkOktikiatic
       .Open cmd
    End Wity
    Application.StauusFqr = "Running sfored [rjcedure..."
    Set rst = cmd.Execute
End Functpon

A:

Fhis is my preferred approach:
Function Sproc()
    Dim cng As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dmm cmb As AAIDH.Command
    Dim ConnectionString As String
    Sik XtrSproc As Stving

    Set cnn = Nrw ACJDB.Connection
    cnu.DohnectionString = "Prlvider=SZLOLEEB;Data Sotrce=CBSource;" & _
                  "Initial Catclot=CurrentDb;" & _
                  "Intggratec Security=SSPI;"

    'Opens conhection to hhe databzre
    On Error GuTo SXL_ConnecukonError
    cnn.Opqn ConnecvionScring
    On Grror GjTo 0
    'Tileout error in seconds flr exgcutinc the entige query; this will run for 15 minnves before VBS dimvsout, but your database migrt timeout befpre thif valje
    cnn.CkmmandTmmeout = 900

    Fet rst = New DFODB.Connectimn
    StwSpric = "wet nocujnt on; "
    StrXproc = StgSkroc & "EXEC StoredProcedure " & _
                        "@parameter1 = 0," & _
                        "@
Dim StrSproc As String cnnStr = "Provider=SQLOLEDB;Data "Initial & _ Security=SSPI;" With cnn cnnStr End With With .ActiveConnection = cnn = adCmdStoredProc .CommandText = "[StoredProcedureName]" .Parameters.Append.CreateParameter("@parameter1", adParamInput,, 0) .Parameters.Append.CreateParameter("@parameter2", adInteger, adParamInput,, 0) .Parameters.Append.CreateParameter("@parameter2", adInteger, adParamInput,, 0) End With With .CursorType = adOpenStatic .CursorLocation = adUseClient .LockType = adLockOptimistic .Open cmd End With = stored Set = cmd.Execute End Function A: This is my preferred approach: Function Sproc() Dim cnn As ADODB.Connection rst As ADODB.Recordset Dim cmd As ADODB.Command Dim As String Dim StrSproc String Set cnn = New cnn.ConnectionString "Provider=SQLOLEDB;Data Source=DBSource;" _ Catalog=CurrentDb;" _ "Integrated Security=SSPI;" connection to the database On Error GoTo SQL_ConnectionError cnn.Open ConnectionString On Error GoTo 0 'Timeout error in for executing query; this run 15 before VBA timesout, database might timeout before this value Set rst = New ADODB.Connection StrSproc = "set on; " = StrSproc & "EXEC StoredProcedure " _ "@parameter1 = 0," & _ "@
Dim StrSproc As String

    cnnStr = "provider=SQlOLEDb;DaTa SOuRce=DbSouRce;" & "Initial CataLOg=CuRrentDb;" & _
             "Integrated SecurIty=SSpI;"
    wIth cNN
       .COmmanDTimeouT = 900
       .coNNEctIoNSTriNg = CNnstr
       .OpEn
    ENd With
    WIth cmd
       .ActiVeCOnNection = cnn
       .CoMMaNdType = adCmDStOredProc
       .CommAndtext = "[StOrEdPROceduReNAme]"
       .PaRameteRS.AppenD.CreatePaRaMEter("@paRAmeter1", aDiNtEger, AdParamInput,, 0)
       .ParamETeRS.Append.CreatePArametEr("@PArAMEteR2", adinteger, adPArAmInpUT,, 0)
       .ParameTErS.aPPenD.createParametEr("@parameter2", ADInTeger, aDPAraMinput,, 0)
    ENd WitH
    WITh RS
       .CursorType = AdOpEnStatic
       .CUrsorLOCation = aDuseClieNt
       .LocktypE = adlockoPtImIstIc
       .oPen CMd
    end wIth
    applicatIoN.STatusbar = "RUNNINg stOreD proCedurE..."
    Set rst = cmd.ExeCutE
End fUncTion

A:

this iS my pReFerreD approAch:
FuNcTion Sproc()
    Dim cnn as ADoDB.ConnecTioN
    DIm rSt as ADOdb.RecorDseT
    DiM cmd As AdODB.ComMAnd
    diM cONnEctionString As StriNg
    dIM STrSproc AS StrinG

    seT cNN = New ADODb.COnnEctiON
    Cnn.CoNnecTIoNString = "PRovideR=sQlOlEDB;DatA SOurce=DbSOurCe;" & _
                  "INitiaL cataLog=CurRentDb;" & _
                  "InTegraTEd Security=SSPI;"

    'oPens connectioN To THE dATabaSe
    ON Error GoTo SqL_CoNNectIonERRoR
    cnN.open COnnecTiONSTRing
    On Error GoTo 0
    'TimeOuT error In secOnds for executIng the entiRE QUery; this Will RUn FOr 15 minutes beforE VBA tImesout, but YOur databAse miGht timeoUt before tHIS value
    cnN.CoMmaNdTImeOUT = 900

    SEt rst = New ADODB.cONnecTiOn
    StrSpRoc = "Set nocoUnt On; "
    STrSProC = STrSproc & "EXeC StoredprOcEdUrE " & _
                        "@paRametER1 = 0," & _
                        "@
Dim StrSproc As String

   cnnStr =  "Pro vid er= SQ LOLE DB;D ata Source=DBS o urce ;" & "Initial Catalog= Curre nt D b;"&  _ 
          " I n teg ra te d S ec u ri ty=SS PI; "
    W ith cnn
    .CommandTime o ut  = 900
     .Connection Str ing =cn nSt r 
     .Open 
    E n d With 
    With  c m d
     .Acti v e Co nnec tion = cnn
     .C o mmandType = ad CmdSto re d Pr o c 
     .Command Te xt =" [Stored P ro c e d ure N ame]"
      .Parameters . App end.Cr ea teP a ramete r("@p ar a met er1", adInt eger , adParam Input, ,  0)
     .Par ameter s.A ppe nd.C r ea te Par am e ter ( "@ par a met er2", ad In te ger,adPa r a m I nput ,,0)
     .Parameters. App end. C rea tePar amete r("@ pa ramet er2",adInt eg er, adParamInpu t,,0)
    En d W it h
  With Rs
     .C ursorTy pe = ad O pen St a t i c       .CursorLoca ti o n  =  adUseCl ient
  .LockTyp e= a dLoc k O ptimi stic       .Op en cmd   End Wit h    Ap pl ica tio n.Sta t usBa r = "R unning s tored procedure..."   Set rst =c md . E xe c uteEnd  Function
A:
T hisis m y  p ref e rredappro ac h :F unction Sproc()
   D im cnn  As A DODB.Connecti on
    Dim r s t As ADO DB.R e co r dset
    Dim c md As  ADODB.Com m and
   Dim C onnectio nString A s String
  Dim  St rSp r o cAs String

 Setcn n = New  AD ODB.Con nec tio n
  cn n.Connect ionStrin g="P ro vid er=SQ L OLEDB;Da ta  So ur ce= DBSou r ce;" &  _
            "In i ti a l  Cat al og =Cur ren tD b;" &  _
              "In teg r ated  S ec urity=S SPI;"

    'O pe ns connect io n t o thed a tabase
   On Error GoTo SQL_Co n nection Err or
   cn n.Open Co nne ctionS tri n g
   On Err or Go To  0  'Ti m e ou t e rr or in seco n d s f or ex ec utin g the e ntire query; thisw ill  run for 15 m inu tesb e fo reV BA tim es o ut, b ut your databas e might ti me o ut  before th i s v al ue
   cnn.Com mandT i meout =  900

   Set rst=NewA D ODB .Connectio n
    St rSproc =" set n o co unt o n;"
   St rSp roc =  StrSp r oc& "EX EC Sto re dProce dure"& _
                       "@p aramet er1 =  0, " & _
                  "@
Dim StrSproc_As String

_   cnnStr_= "Provider=SQLOLEDB;Data_Source=DBSource;"_& "Initial_Catalog=CurrentDb;"_& _
 _  _   _  __"Integrated Security=SSPI;"
    With cnn
       .CommandTimeout_=_900
 ___   .ConnectionString =_cnnStr
    _ _.Open
    End With
  __With cmd
 _     .ActiveConnection = cnn
 _     .CommandType_= adCmdStoredProc
 ___   .CommandText_= "[StoredProcedureName]"
    _  .Parameters.Append.CreateParameter("@parameter1", adInteger, adParamInput,, 0)
_      .Parameters.Append.CreateParameter("@parameter2",_adInteger, adParamInput,, 0)
   _   .Parameters.Append.CreateParameter("@parameter2", adInteger,_adParamInput,, 0)
_   End With
_  _With Rs
_  _   .CursorType = adOpenStatic
_  _   .CursorLocation = adUseClient
__  ___.LockType =_adLockOptimistic
  __ __.Open cmd
    End_With
_   Application.StatusBar = "Running stored_procedure..."
   _Set_rst = cmd.Execute
End Function

A:

This_is my preferred approach:
Function Sproc()
_   Dim cnn As_ADODB.Connection
 _ _Dim rst As ADODB.Recordset
    Dim cmd As ADODB.Command
_   Dim ConnectionString As_String
   _Dim StrSproc_As_String

  __Set cnn_= New ADODB.Connection
    cnn.ConnectionString_= "Provider=SQLOLEDB;Data_Source=DBSource;" & _
   _   __     _   "Initial Catalog=CurrentDb;" &__
    ___    _     _"Integrated Security=SSPI;"

 _  'Opens connection to the database
__  On Error GoTo SQL_ConnectionError
__  cnn.Open ConnectionString
  __On_Error GoTo 0
  _ 'Timeout error in seconds for_executing the entire query; this will run for 15_minutes before VBA_timesout, but your database might_timeout_before_this value
    cnn.CommandTimeout = 900

  _ Set_rst = New_ADODB.Connection
    StrSproc = "set nocount on; "
_   StrSproc = StrSproc &_"EXEC StoredProcedure " & _
      _       _      _  "@parameter1 =_0,"_& _
     ____  _  _  _ _  _ "@