question

harrer avatar image
harrer asked

SQL Server 2014 Linked Server to Access mdb file

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.
linked-servermicrosoft-access2014
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
David Wimbush avatar image
David Wimbush answered
If it's Access 2000 you probably need to use the older Jet provider in your connection string. See here for details: https://www.connectionstrings.com/access/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.