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.

Use this..

Exec sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

Exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1; 
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1;
GO

Insert into OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=C:\upload_test.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
SELECT ColumnNames FROM Your_table -- Sheet Should be already Present along with headers

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 0;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 0;
GO

Exec sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO

Exec sp_configure 'show advanced options', 0
RECONFIGURE;
GO

up vote
0
down vote
Close SQL Server Management Studio. Type Services.msc in the run command to open the services window.

Search for SQL Server Service and right click it and select properties.

In the Log On Tab, select system account/or select your domain ID and Account and password.

Once it finds your login name press OK.

Now type your login’s passwords in both the fields.

Restart the services so that the new changes are applied as shown in figure below.

Now start SQL Server Management Studio and try to run the query if still not working try a system restart.

or execute the query.

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

I have simply enter in SQL Management Studio as Administrator and the SELECT statement run well,

In fact both of them:

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
--'Data Source=C:\xxx.accdb;Persist Security Info=False;')...nTypeQuantity ;

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0';,
'C:\xxx.accdb';'admin';''
,nTypeQuantity);