title: Excel 2007 forms to SQL Server
body: <p>I've been searching everywhere for an answer with no success to the problem I'll describe.</p>
  
  <p>I have an Excel sheet which gets manually updated frequently. I've been asked to push every row change into an Sql Server, in a live way. I need users to be able to save a row to the server by pushing a save button. </p>
  
  <p>In SSMS I've been able to read data from the server using OPENROWSET, e.g.: </p>
  
  <pre><code>SELECT * 
  FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;HDR=YES;Database=C:\tmp\test2.xlsm',
  'SELECT * FROM [myrng]');
  </code></pre>
  
  <p>I've been also able to insert data from Excel to SQL Server:</p>
  
  <pre><code>Insert into NEWDB.dbo.master 
  Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 
  12.0;HDR=YES;Database=C:\tmp\test2.xlsm','SELECT * FROM [myrng]')
  </code></pre>
  
  <p>The problem arises when I try to QUERY, INSERT or UPDATE data back to the server with the Excel file still open.</p>
  
  <p>What I'm planning to do in the end is to build a wizard app to fill an Excel table which at the end of the process could be submitted to a corresponding SQL Server table. </p>
  
  <p>I've been playing with VBA and ADO with no success so far, and I've been suggested to use DTS or SSIS, but I need the Excel file to stay open all the time.</p>
  
  <p>Thanks in advance,
  Rodrigo.</p>