Run a query in SQL Server on an Access database as linked server

Question

I have been trying to figure this out for about a half of a day now. I have been trying to run a very simple query on an Access database in SQL Server 2014.

Query:

select * from plantsT

what I have tried.

I tried to run

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'\\path\MasterDatabase.accdb';
'';
'', PlantsT);

Error message:

Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

I have tried all of the options on MSDN

Same error.

I have tried to install access 2007 drivers.

I have tried to link as linked server. I was able to link to access file using this code"

DECLARE @AccessFileName nvarchar(32);
DECLARE @FilePath nvarchar(max);
DECLARE @CombinedPath nvarchar(max);

SET @AccessFileName = N'MasterDatabase.accdb';
SET @FilePath = N'Y:\';
SET @CombinedPath = @FilePath + @AccessFileName;

IF  EXISTS (
     SELECT srv.name 
     FROM sys.servers srv 
     WHERE srv.server_id != 0 
     AND srv.name = N'MasterDatabase' ) 
BEGIN
     EXEC master.dbo.sp_dropserver 
          @server=N'MasterDatabase'
END

EXEC master.dbo.sp_addlinkedserver 
     @server = N'MasterDatabase',  
     @provider = N'Microsoft.ACE.OLEDB.12.0',  
     @srvproduct = N'Access2007', 
     @datasrc= @CombinedPath

I tried to connect to a test database with same path and no luck there either. I have checked permissions and shared that folder with everyone.

I have checked and made sure Access is installed on executing server.

How can I run a query on this db?

I have just found out that i think it is the wrong bit level driver please inform me how to fix or work around.

I am confused. You said you could open a connection with a link server. But then you say you can't. If you already have Access installed on your server (not recommended) then the link server would be the way to go. — Sean Lange, Jul 22 '16 at 19:57
I can add the access file as a linked server. i cannot connect to it — JamTay317, Jul 22 '16 at 20:04
no, i can see my connection, but when trying to access any tables before they come up i get same error — JamTay317, Jul 22 '16 at 20:15
I believe your Access file is no open somewhere else and is not locked... just wanted to make sure — techspider, Jul 22 '16 at 20:15
Did you try executing query using an Admin account? I'm suspecting whether the Account you are using has no access to the network path you are trying or on SQL Server services — techspider, Jul 22 '16 at 20:18
See [this](http://stackoverflow.com/questions/36987636/cannot-create-an-instance-of-ole-db-provider-microsoft-jet-oledb-4-0-for-linked), if it is of any use — techspider, Jul 22 '16 at 20:24
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/118062/discussion-between-jamtay317-and-techspider). — JamTay317, Jul 22 '16 at 20:37
In case you haven't seen [this answer](http://stackoverflow.com/a/26829350/2144390) yet, you may find it helpful. — Gord Thompson, Jul 22 '16 at 21:01

Run a query in SQL Server on an Access database as linked server

0 Answers0