title: Calling a stored procedure from Access VBA
body: <p>I need to call a stored procedure from a distant SQL Server into Access using VBA as I need to pass dynamic variables. </p>
  
  <p>I call this SP using a look through query:</p>
  
  <pre><code>Exec GetPortfolioLookthroughMultiPeriod
    @PORTFOLIONAME ='wefb'
  ,@DATE= '2018-02-06'
  , @TYPES='portfolio'
  ,@VALUES = 'portfolio'
  ,@ISLOOKTHROUGH=1
  ,@PORTFOLIOCURRENCY='PC'
  ,@EXPRISKLEVEL='currency'
  ,@NUMBEROFMONTHS=15
  ,@AssetClassStrategyName='fund'
  ,@NUMBEROFPERIODS=1
  ,@FREQUENCY='d'
  ,@ShowDetail=1
  ,@TableNo=6
  </code></pre>
  
  <p>Using the following ODBC Connect String:</p>
  
  <pre><code>ODBC;Description=RiskManager;DRIVER=SQL Server Native Client 11.0;SERVER=APX-TEST01;UID=X.Tran;Trusted_Connection=Yes;DATABASE=RiskManager; 
  </code></pre>
  
  <p>Now my VBA code returns an error:</p>
  
  <blockquote>
    <p>Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.</p>
  </blockquote>
  
  <p>Here is the code I am using:</p>
  
  <pre><code>Dim conn As ADODB.Connection
  Dim cmd As ADODB.Command
  
  Set conn = New ADODB.Connection
  conn.Open "ODBC;Description=RiskManager;DRIVER=SQL Server Native Client 11.0;SERVER=APX-TEST01;UID=X.Tran;Trusted_Connection=Yes;DATABASE=RiskManager;"
  Set cmd = New ADODB.Command
  With cmd
      .ActiveConnection = conn
      .CommandText = "GetPortfolioLookthroughMultiPeriod"
      .CommandType = adCmdStoredProc
      .CommandTimeout = 0
      .Parameters.Append .CreateParameter("@PORTFOLIONAME", adVarChar, adParamInput, 40, "wefb")
      .Parameters.Append .CreateParameter("@DATE", adDBDate, adParamInput, 50, #2/5/2018#)
      .Parameters.Append .CreateParameter("@TYPES", adVarChar, adParamInput, 10, "portfolio")
      .Parameters.Append .CreateParameter("@VALUES", adVarChar, adParamInput, 10, "portfolio")
      .Parameters.Append .CreateParameter("@ISLOOKTHROUGH", adVarNumeric, adParamInput, 10, 1)
      .Parameters.Append .CreateParameter("@PORTFOLIOCURRENCY", adVarChar, adParamInput, 10, "PC")
      .Parameters.Append .CreateParameter("@EXPRISKLEVEL", adVarChar, adParamInput, 10, "currency")
      .Parameters.Append .CreateParameter("@NUMBEROFMONTHS", adVarNumeric, adParamInput, 10, 15)
      .Parameters.Append .CreateParameter("@AssetClassStrategyName", adVarChar, adParamInput, 10, "fund")
      .Parameters.Append .CreateParameter("@NUMBEROFPERIODS", adVarNumeric, adParamInput, 10, 1)
      .Parameters.Append .CreateParameter("@FREQUENCY", adVarChar, adParamInput, 10, "d")
      .Parameters.Append .CreateParameter("@ShowDetail", adVarNumeric, adParamInput, 10, 1)
      .Parameters.Append .CreateParameter("@TableNo", adVarNumeric, adParamInput, 10, 6)
      .Execute
  End With
  </code></pre>