'ad hoc access to OLEDB provider denied' after trying VBA addin on different client machine

Question

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):

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$]')

The above code still works - I've been invoking this sProc from a VBA addin launched to process an Excel 2003 workbook opened on my WindowsXP machine but I'm migrating away from XP....

So, here is the problem:

I've copied this VBA addin to another machine (WinServer2003) and loaded it for processing the same workbook with Excel 2010. The addin runs fine until the stored procedure is invoked. Then I get this message:

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

Searching on that message I've discovered:

• I have no linked servers defined in my SQL Server 2008 instance
• The Surface Area Configuation facet for AdHocRemoteQueriesEnabled is TRUE
• 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

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.

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).

EDIT-UPDATE: Attempt to change provider per comment below led to error message (i.e. which is a different issue):

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.

The Jet provider shown here continues to work when invoked from a different client machine as stated above.

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:

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)".

So, clearly I am chasing stuff I really don't know here. Thanks in advance for your continued help.

Answer 1

The comment from Rory above provided a good clue. This is an area that I've not read about for several years. In short, using a new client machine and hence Excel 2010 (versus Excel 2003) means I needed a new "provider" to process the data. The article http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm is excellent. I had to install the provider, update the registry, run some special system stored procedures (all of these adjustments are not obvious but nicely explained in the article). Finally, I had to modify the OPENROWSET parm to use the ACE.OLEDB provider instead of JET.OLEDB and Excel 12.0 instead of Excel 8.0. Nothing else needed to change. After this, it even worked from a different client machine running Excel 2007!