title: Two ways to execute a Stored procedure in VBA, Which one is better?
body: <p><strong>Background:</strong> Work on frontend Ms-Access 2010 and backend SQL server 2008 Managment Studio</p>
  
  <p>For executing stored procedures I have been using a pretty lengthy process as seen here: <em>in VBA</em></p>
  
  <pre><code>    Set Conn = New ADODB.connection
      Conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;....."
      Conn.Open
  
      Set cmd = New ADODB.Command
      cmd.ActiveConnection = Conn
      cmd.CommandType = adCmdStoredProc
      cmd.CommandText = "upGetTestIdForAnalyte"
  
      cmd.Parameters.Append cmd.CreateParameter("@WOID", adVarChar, adParamInput, 60, MySampleName)
      cmd.Parameters.Append cmd.CreateParameter("@Analyte", adVarChar, adParamInput, 60, MyAnalyte)
      cmd.Parameters.Append cmd.CreateParameter("@SampleID", adVarChar, adParamInput, 60, MyConcentration
  
      cmd.Execute
      Conn.Close
  </code></pre>
  
  <p>Someone told me there was a better way to execute a stored procedure and the correct way would be something like this: <em>in VBA</em></p>
  
  <pre><code>strsql = "Exec upGetTestIdForAnalyte(WOID, Analyte, SampleID)"
  test = ExecuteNonQuery(strsql)
  </code></pre>
  
  <p>But I got a lot of errors for this process and I looked up ExecuteNonQuery and it said it was only for VB (<a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx</a>). One of the advantages of this process would be its conciseness and the fact that it connects automatically to the current database.  I would have to change the connection string in my current sp process because it is set to link to a test database not the actual one.  Another advantage would be that it returns a value when executed.  I would like my current process to do that because I want to somehow verify it ran and other stored procedures I need to make, need to return the number of records affected.  </p>
  
  <p><strong>My questions are:</strong>  Is the second method a legitimate process and is that the correct syntax?  Also is there any difference in what each process accomplishes?  Also is there a way for the first process to return a value when executed?  Thank you for your time.</p>
  
  <p><strong>UPDATE:</strong> This is my stored procedure I'm currently working on.  My sp sees if a testID exists or not, I will continue with the program after calling the sp if variable ThisTestId >0 else I will raise an error testid not found</p>
  
  <pre><code>CREATE PROCEDURE upGetTestIDForAnalyte @WOID nvarchar(60), @SampleID nvarchar(60),@Analyte nvarchar(60), @Test var(20) output
  AS
  
    SELECT TestID = t1.TestID
    FROM tblWOSampleTest t1
    JOIN tblTest t2
    ON t1.TestID=t2.TestID; 
    WHERE @WOID = t1.WOID AND @SampleID = t1.SampleID AND @Analyte = t2.Analyte
  
  GO
  </code></pre>