Update that may be helpful I am using SQLExpress v 15 and Management Studio 19. I installed accessdatabaseengine_X64 and ran
`USE [master] GO
EXEC sp_configure 'Show Advanced Options', 1 RECONFIGURE GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE GO
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1 GO
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1 GO`
Then I ran the openrowset query....
--build new table SELECT * into MyTable from OPENROWSET('Microsoft.ACE.OLEDB.16.0', 'Excel 12.0;Database=C:\ExcelFiles\Book2.xlsx;HDR=YES', 'select * from [Sheet1$]')
Everything was roses... Shut down the machine and started in back up the next day and ran .....
--build new table SELECT * into MyTable from OPENROWSET('Microsoft.ACE.OLEDB.16.0', 'Excel 12.0;Database=C:\ExcelFiles\Book2.xlsx;HDR=YES', 'select * from [Sheet1$]')
Got this error... "Cannot initialize the data source object of OLE DB provider ACE.OLEDB.16.0"
Spent the next X hours trying to figure out what happened. Logged out and logged in as 'sa' and it all worked....--> windows security issue then.
Rebooted the machine and got the error again...found this solution. --I will summarize here..-> my windows authenticated login for SQLExpress did not have permissions on the SQLExpress service --
Solution 2 Check whether the user login that you are accessing has rights on SQL Server Service or not. If not you will have to give your credentials so that the SQL Service runs using your login credentials.
Steps
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 This Account. Click on Browse and type your login name there and the click on Check Names
Once it finds your login name press OK.
Now type your login’s passwords in both the fields as shown below press apply and then ok
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
--I did the services restart and it worked and a reboot and it worked. here is the link -- it was for oledb.12 but it also works for oledb.16 --solution 2