Query Access table in SSMS

[vqcheese]

Heres my scenario.
I have an access table called Innovative in an access database.

While on the server i can query this
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'\\vq1\Efile Document Storage\HR\HR\Employee File Notes\Employeefilenote.accdb'; 'admin';'',innovative);

so i have made that into a view called bk_Innovative. When i want to query that from SSMS loaded on my machine not the server i get this 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 '\\vq1\Efile Document Storage\HR\HR\Employee File Notes\Employeefilenote.accdb'. It is already opened exclusively by another user, or you need permission to view and write its data.".
Msg 7303, Level 16, State 1, Procedure bk_Innovative, Line 6
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

im a network admistrator, i have access to that path, so why if i make that into a view on the server, cant i query that out on my pc. it only works to query on this server. I want to use this query in Reporting services.
Any IDEAS?

 

[Rx_]

That is a good question. Let me suggest you go to http://www.sqlservercentral.com/Forums and ask it in the Newbie area
My guess is that it will require a SP to call. Then the SP will be inside SQL to call the outer link. I have been wrestling with an Oracle Linked Server over a dog slow VPN and got some excellent advice there.
When it is out of the ordinary, this is a great place to goto.
Please be sure to post your answer if they provide one. It sounds very interesting.