Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 SQL 2012 Linked server to MS Access DB

Author  Topic 

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2014-10-21 : 11:06:49
Hi all,

I've got a SQL Server 2012 instance and I'm trying to create a linked server to a Microsoft Access database.

I have installed the Microsoft Access Database Engine 2010 (AccessDatbaseEngine_x64) on the machine and selected the provider - Microsoft Office 12.0 Access Database Engine OLE DB Provider

I'm getting the following error message when I create the linked server:

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server MyLinkedServer
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MyLinkedServer" returned message "Unspecified error"


Is there any other driver I need to install?

Has anyone ever got this to work?

It worked fine for me in SQL 2008R2.

By the way I have selected the Option 'Allow In Processes' and it doesn't work.

Thanks

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2014-10-21 : 12:09:44
I started Management Studio with the 'Run as Administrator' option and it worked.

So where could the permission issue be?

What do I need to change?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-21 : 12:43:51
Disable UAC and run management studio normally (Not run as administrator) and see if that works. If it does, then grant explicit permissions to the folders where the access file is located to the SQLServerMSSQLUser group. I have seen that sometimes inherited permissions don't work properly, so you might try granting the permission explicitly to the group.
Go to Top of Page
   

- Advertisement -