title: Setting NO COUNT ON in VBA ADODB
body: <p>After hours of frustration I have realized that I have become a victim of <a href="http://support.microsoft.com/kb/235340/en-us" rel="nofollow">Error Messaging Referencing #Temp Table with ADO-SQLOLEDB</a></p>
  
  <blockquote>
    <p>When you try to create a ADO recordset based on a #Temp table created
    within a stored procedure called by ADO using the SQLOLEDB provider,
    you may encounter one of the following error messages...</p>
  </blockquote>
  
  <p>The solution is to add <code>SET NOCOUNT ON</code> to either the Stored Procedures, or to the VBA code when the recordset is open.  At the moment I am stuck with adding it to my VBA code.</p>
  
  <p>The example code that MS gives as a solution is something like:</p>
  
  <pre><code>Dim cmd As ADODB.Command
  Set cmd = New ADODB.Command
  cmd.ActiveConnection = conn
  cmd.CommandText = "spThatHasTempTable"
  cmd.CommandType = adCmdStoredProc 
  ..
  
  
  Dim rs As ADODB.Recordset
  Set rs = New ADODB.Recordset
  With rs
      Set .ActiveConnection = conn
      .Open "SET NOCOUNT ON"
  End With
  
  rs.Open cmd, , , , adCmdStoredProc
  </code></pre>
  
  <p>This works for me.  However I feel like that setting <code>SET NOCOUNT ON</code> really should be a part of the <code>Command</code> object construction, but I can't seem to figure out how to do just that.</p>
  
  <p>So how would I include <code>SET NOCOUNT ON</code> when constructing the <code>Command</code> object?</p>