I am running Windows Server 2003. I have SQL Server 2005 installed and I have Access 2007 installed.
I have two SQL stored procedures to import excel files into SQL tables. The one that imports files with the older .xls extension use the Access JET provider. That sp is working just fine. The other sp for importing the newer .xlsx files fails. The ASP.NET executing the sp returns: Ad hoc access to ole db provider.... bla bla bla.
I had the same problem with using JET. I just had to go to the registry and navigate to HKLM\software\microsoft\microsoft sql server\mssql.1\providers\microsoft.jet.oledb.4.o and create a key called Disallowadhocaccess.
HOWEVER, I don't have any registry path under sql server that has a key named like Microsoft.Ace. That makes me think that I don't have that provider installed (even though office 2007 installed).
Can anyone tell me ... do I need to download and install that provider or should I just make a registry path? Note that under sql server I have MSSQL.1 through MSSQL.5.