title: 'ad hoc access to OLEDB provider denied' after trying VBA addin on different client machine
body: <p>I have a stored procedure in SQL 2008 that has been used for many years without problems. Here is a relevant snippet (part of an INSERT INTO table SELECT block with only few columns shown):</p>
  
  <pre><code>INSERT INTO dbo.myTemp_Excel
  SELECT 
  'PropertyState'     = [Property State] ,
  'PropertyCounty'    = [Property County] 
  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'Excel 8.0;Database=D:\RawData_MDS\Book1.xls;HDR=Yes;IMEX=1', 
      'SELECT
           [Property State] ,
           [Property County]
       FROM [Sheet1$]')
  </code></pre>
  
  <p>The above code still works - I've been invoking this sProc from a VBA addin launched to process an <strong>Excel 2003</strong> workbook opened on my <strong>WindowsXP</strong> machine but I'm migrating away from XP....</p>
  
  <p>So, here is the problem: </p>
  
  <p>I've copied this VBA addin to another machine (WinServer2003) and loaded it for processing the <strong>same workbook</strong> with <strong>Excel 2010</strong>. The addin runs fine until the stored procedure is invoked. Then I get this message:</p>
  
  <p><strong>Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server</strong>.</p>
  
  <p>Searching on that message I've discovered:</p>
  
  <ul>
  <li>I have no linked servers defined in my SQL Server 2008 instance</li>
  <li>The Surface Area Configuation facet for AdHocRemoteQueriesEnabled is TRUE</li>
  <li>On my server machine, the Registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Providers\SQLNCLI10 has an entry called DisallowAdhocAccess which 0</li>
  </ul>
  
  <p>So my SQL server shows me no clues that I might have done long in the past to enable the addin to fire the sProc from my XP client machine.</p>
  
  <p>Any ideas to resolve this problem? The only thing really different is the "client" machine (i.e. the SQL instance, sproc, and addin code all the same).</p>
  
  <p><strong>EDIT-UPDATE:</strong>
  Attempt to change provider per comment below led to error message (i.e. which is a different issue):</p>
  
  <pre><code>Msg 7403, Level 16, State 1, Procedure _Job_ConvertRawFHADataV3_PrepareStep, Line 59
  The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.
  </code></pre>
  
  <p>The Jet provider shown here continues to work when invoked from a different client machine as stated above.
  <img src="https://i.stack.imgur.com/Toob2.png" alt="enter image description here"></p>
  
  <p>However, maybe the JET provider cannot handle the same exact workbook when opened from Excel 2010. The syntax on the OPENROWSET shows Excel 8.0 which I assume corresponds to Excel 2003 since that works. I tried changing the sProc to Excel 12.0 and got this: </p>
  
  <pre><code>OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
  Msg 7303, Level 16, State 1, Procedure _Job_ConvertRawFHADataV3_PrepareStep, Line 59
  Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
  </code></pre>
  
  <p>So, clearly I am chasing stuff I really don't know here. Thanks in advance for your continued help.</p>