title: MS SQL get values after INSERT
body: <p>I am facing an issue where I need to calculate some data based on existing data, then insert that data and finally, return it to an Excel file using VBA.</p>
  
  <p>I have successfully been able to create a stored procedure that returns a table of values after inserting:</p>
  
  <pre><code>[...]
  INSERT INTO [ExcelRGA] (RM_prefix, RM_suffix, editing, createdBy, editedBy) values (@RM_prefix, @RM_suffix, 1, @user, @user);
  
  DECLARE @tab table (RM varchar(20))
  
  INSERT @tab SELECT (@RM_prefix +'-' + right('00' + @RM_suffix, 3)) as 'RM';
  
  SELECT * FROM @tab  
  END
  </code></pre>
  
  <p>and this works! However, I am unable to get the values it is returning using VBA</p>
  
  <pre><code>Set oRS = New ADODB.Recordset
  Set cmd = New ADODB.Command
  Dim objRec As Object
  cmd.ActiveConnection = oCon
  cmd.CommandType = adCmdStoredProc
  cmd.CommandText = "dbo.newRM"
  
  cmd.Parameters.Refresh
  cmd.Parameters("@user").Value = "john"
  
  Set oRS = cmd.Execute()
  </code></pre>
  
  <p>but I try to do something like</p>
  
  <pre><code>while not oRS.eof
     ....
  wend
  </code></pre>
  
  <p>get an error message stating that the recordset is closed and I cannot do my thing.</p>
  
  <p>All I am trying to do is secure the information that I have computed (RM_prefix and RM_suffix), insert them into my table and return them to my Excel file.</p>
  
  <p>If there is a way to do this without using a store procedure (such as a function) that would also be great!</p>
  
  <p>Please keep in mind that this is a multi-user environment, so generating the values in Excel, sending them to the SQL server for an insert doesn't give a 100% guarantee regarding the uniqueness of said data.</p>
  
  <p>Thank you all for your precious help and inputs!</p>
  
  <p>Peter</p>