SQL Server – List All Objects in LinkedServers Providers

I found the issue but I'm not sure why its happening.

To query the linked server I have to right click SSMS and select Run as Admin. Then I can query the access db.

I dont know why this is the case as I'm remoted into the machine. I'm an administrator on the machine. I created the Linked Server to the Access Database which resides on the C:\Drive of the server.

But this will at least allow me to hand run my code. Since this is my development server I think that will suffice at least for now.

Ok well I found the solution or at least its one or both of the two

1. Instead of running express with the Network Service account, I created a regular user. Then I gave that user rights to the folder with the excel spreadsheets.

2. I had already installed AccessDatabaseEngine.exe but I hadn't restarted the computer. This time I restarted it.

This is the OPENROWSET SELECT statement I used

SELECT FT_ID_SOCIETE_FF2C,FT_NOM,FT_ADR1,FT_ADR2,FT_CP,FT_VILLE,FT_TEL,FT_FAXFROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\SQLFTP\LISTEFF3CFTP.xls;HDR=YES', 'select * from [Requêtechauffage fioul$]');

So maybe it was a permissions problem? Weird since I could import csv files. Maybe it was just having to restart the computer after having installed AccessDatabaseEngine.exe.