Connecting Access database from SQL Server Management Studio

[HerrSober]

Hi All,

I have most my local Access databases stored on the desktop, however I prefer to work with SQL Server Management Studio. Is there any way of connecting to local Access databases without admin rights?

Best regards
Sober

 

[xenou]

Is this part about admin rights relevant? I'm confused as I don't see why that would matter.

 

[HerrSober]

Is this part about admin rights relevant? I'm confused as I don't see why that would matter.

Hi Xenau,

I only mentioned admin rights as one approach I tested returned missing admin rights as part of the error message, so please disregard this minor detail.

Best regards

 

[xenou]

I'd be curious to know what method you tested then - it might be a good place to start.

 

[HerrSober]

I'd be curious to know what method you tested then - it might be a good place to start.

I tried to add a new query with the following statement:

----EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

USE [DatabaseName]

GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.14.0','Data Source="C:\Databases\dbNorthwind.accdb"')...tblCustomers;

It returned the following error:
"Msg 5812, Level 14, State 1, Line 3
You do not have permission to run the RECONFIGURE statement..."

 

[xenou]

Why are you using RECONFIGURE?
Have you tried not using that?

 

[HerrSober]

Why are you using RECONFIGURE?
Have you tried not using that?

Yes, if I run

[COLOR=#333333]SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.15.0','Data Source="C:\Databases\dbNorthwind.accdb"')...tblCustomers;[/COLOR]

I get:

Msg 7403, Level 16, State 1, Line 2. The OLE DB provider "Microsoft.ACE.OLEDB.15.0" has not been registered.

(also tried with Microsoft.ACE.OLEDB.14.0). I currently run Access 2013 32-bit.

 

[xenou]

You are missing the ACE OLEDB driver.