question

I'm having trouble connecting to an Access mdb file. I only want to query data from this Access file, I don't want to import or anything. I have SQL Server 2014 Standard Edition - (Microsoft SQL Server 2014 (SP2-CU1) (KB3178925) - 12.0.5511.0 (X64) Aug 19 2016 14:32:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor) ). The server is Windows Server 2012 R2, 64 bit. So I setup a linked server and it didn't work. Then I configured and setup the existing provider with Allow In Process and Dynamic Paramenters. SQL was: SP_CONFIGURE 'show advanced options', 1; GO RECONFIGURE; SP_CONFIGURE 'Ad Hoc Distributed Queries', 1; --was 0 GO RECONFIGURE; EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1; EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1; I then restarted the SQL Server service from SSMS. And setup a new linked server: exec sp_addlinkedserver @server='Z', @srvproduct='Access', @provider='Microsoft.ACE.OLEDB.12.0', @datasrc='D:\SQLData\Z\Z.mdb' exec sp_addlinkedsrvlogin @rmtsrvname='Z', @useself='false', @rmtuser='Admin', @rmtpassword='' The linked server added fine, but then drilled down to Catalogs / default / Tables and got this error: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Z". (Microsoft SQL Server, Error: 7303). The SQL Server service uses Log On As: NT Service\MSSQLSERVER, and that user has permissions on that folder. Any help would be much appreciated. Per the comment below regarding https://social.msdn.microsoft.com/Forums/sqlserver/en-US/96ca13a3-a39e-4271-aa93-86e20067544f/linked-server-error-7303?forum=sqldataaccess - I hesitate to change the service to be under local system, as other people have set it up, even for our development box. I gave full control to "everyone" to C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp, and turned AllowInProcess to 0, then restarted the SQL Server service, but this didn't work. --And I did restart SQL Server service running as Local System, and granted Local Service access to C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp without any luck. In answer to Dave W's question: I don't know about the Access version, could be 2000, I just know the file extension is mdb. I can't open it as I have Office 2013.