Problems while executing OPENROWSET 'Microsoft.ACE.OLEDB.12.0'

Problems while executing OPENROWSET 'Microsoft.ACE.OLEDB.12.0'

I was getting different errors at every stage when executing the OPENROWSET command.

Steps which i used,

--CREATE
create table [my_chema].stuff_test( stid varchar(100), sname varchar(100));
--INSERT
insert into [my_chema].stuff_test values( 'S100','Maths');
insert into [my_chema].stuff_test values( 'S100','Science');
insert into [my_chema].stuff_test values( 'S100','Social');
insert into [my_chema].stuff_test values( 'S100','Computer');
--SELECT
select * from [my_chema].stuff_test;
--INSERT USING OPENROWSET
insert INTO [my_chema].stuff_test select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:\Users\admin\Documents\stuff_testXL.xlsx', [Sheet1$])

Errors what I received,

Error Message 1:

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

Solution :

USE [my_chema]
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
USE [my_chema]
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

Ref :

https://stackoverflow.com/questions/14544221/how-to-enable-ad-hoc-distributed-queries

Error Message 2:

Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Solution :

USE [my_chema]
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
USE [my_chema]
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParam', 1
GO
On newer SQL Server 2014 You had use 'DynamicParameters' instead of 'DynamicParam'

USE [my_chema]
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

Make sure you register msexcl40.dll like this:
regsvr32 C:\Windows\SysWOW64\msexcl40.dll