I have been working on a website for a client where they need to be able to upload Excel 2007 files with updates for the data the website uses.
In order to easily extract the data from the excel file from my .Net code, I have been treating it as a database by connecting to it using the Microsoft.ACE.OLEDB.12.0 driver with a connection string like this (The following connection string should be all on one line):
Extended Properties="Excel 12.0 Xml;HDR=YES";
My current development machine is a 64bit version of Windows 7 RC box (haven’t installed the release version yet) with Visual Studio 2008. My site has been working fine using the Cassini web server built into Visual Studio 2008. However, when I tried to change the startup options to using the local IIS instance, I was receiving the following error:
"The ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine."
After some searching around, I found a number of blog and/or forum postings that addressed various possible solutions.
IIS 7.0, Access 2007 and ASP.NET 2.0 by Steve Scholfield in which he suggests installing the driver. However I knew the driver was already installed on my machine because I had been using it fine in Visual Studio’s web server.
Other forum postings seem to repeat variations of a suggestion in a post by Thomas Deml, Group Program Manager for IIS at MS where he suggests a command similar to the following:
%windir%\system32\inetsrv\appcmd set config -section:system.applicationHost/applicationPools -applicationPoolDefaults.processModel.loadUserprofile:false
However, I wasn’t convinced that security was my problem. Finally a came across a blog posting on Chris Crowe’s (IIS MVP) blog. In that post he suggests that the problem he was having when receiving the "The ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine." error had to do with the fact that there was not a 64bit driver, and his program was being run as 64bit. He suggested compiling the program to run as 32Bit rather than 64bit.
With an ASP.Net website in Visual Studio 2008, you don’t get to set the output. Instead, the fix appears to be to configure II7 to run your program to access 32bit stuff. So….
To fix it (at least this is what works for me):
- Open IIS Manager
- Select "Application Pools" under your server in the "Connections" panel on the left.
- Select your application’s application pool from the "Application Pools" panel in the middle.
- Right Click on your Application Pool and select "Advanced Settings…"
- Set the "Enable 32-Bit Applications" setting to True
I should mention that I DO have User Account Control settings at the default, and am having to run IE8 using the “Run as Administrator” option because I am also trying to use Windows Authentication to access the site. They may be part of the reason I don’t have a security problem where as some other folks do. I don’t know that for sure though because I have not tested it.