I've noticed a lot of good info here, so I'm hoping this community can help. For days now, I've been trying to find a way to get the below statement to run:
INSERT INTO table SELECT * FROM
'SELECT * FROM [Sheet1$]')
However, I keep getting the error:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine cannot open or write to the file '\\server\folder\file.xls'. It is already opened exclusively by another user, or you need permission to view and write its data.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
I am using SQL Auth to login to the server (running a command via some c# code), but I have created a credential with the Windows AD Login needed to access the shared network file and mapped it to my SQL login in the server management tool. I know the AD login has full rights to that folder, but it still seems to not be able to access it. I have seen several people with this issue, but no solution that seems to work.
Any help or suggestions would be appreciated. I'm running out of ideas.