I am facing a problem while loading & importing an Excel sheet into a SQL Server database.
The error is
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Server PREM4, Line 3
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLE .12.0" for linked server "(null)"
I could not find out what the issue is.
After some careful thought and some experimentation, the AccessDatabaseEngine.exe installed on the server is probably part of the problem. There are actually two versions 32 bit and 64 bit. On my computer 64 bit, I have the 32 bit version of Office installed. The SQL Server on my computer is 64 bit. When I attempt to use openrowset with the same OLEDB provider on a spreadsheet, I get the same error as you. I can't install the 64bit version, without removing the 32 bit version of Office.
Here are some links to two different versions. The second one has both the 32bit and 64bit.
Access Database Engine 2007 32 bit
Access Database Engine 2010 32 bit and 64 bit
The lesson here is; be sure to install the version 32 bit or 64 bit that matches your SQL Server install. If you have a 64 bit server and 32 bit MS Office components installed, you won't be able to install the 64 bit version until you remove 32 bit MS Office.
Once you have the Access Database Engine that matches your SQL Server (64 bit or 32 bit), you can test the provider directly using a query to see if you can import from the spreadsheet.
OLE 12.0
SELECT *
FROM OPENROWSET
('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\somefile.xls;
HDR=YES', 'select * from [name_of_spreadsheet_in_file$]');
OLE 4.0
SELECT *
FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\somefile.xls;HDR=YES',
'select * from [name_of_spreadsheet_in_file$]');
