Linked Server Access 2007 - SQL Server Forums

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 Programming

Linked Server Access 2007

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2012-11-08 : 15:23:19

Hi,
I am creating a linked server on my SQL 2008 on a server with MS Office 2007 installed. I ran the following :

USE [master] 
GO 
EXEC master.dbo.sp_addlinkedserver @server = N'ACCESS', @srvproduct=N'access', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'\\blmcik\AccesQuery\SwitchDatabases\switch measures.accdb'
GO 

USE [master] 
GO 
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'ACCESS', @locallogin = NULL , @useself = N'False' 
GO

And I get the connection error saying:
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Access". (Microsoft SQL Server, error: 7302)

I also ran the following with no success:

USE [master]
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-08 : 16:13:43

That sounds almost like you don't have the ACE OleDB driver registered. Yet, you do have Office 2007 installed and that should have installed those drivers.

I tried the first two commands you listed on my box which has
SQL 2008 R2 and Office 2007 and they worked correctly. I could see the linked server from SQL.

In any case, I would check if you have the dlls - they would be in C:\Program Files\Common Files\Microsoft Shared\Office12 or Office14 or under corresponding directories under Program Files (x86) and named ACEOLEDB.DLL

infodemers
Posting Yak Master

183 Posts

Posted - 2012-11-08 : 16:26:51

I could see the DLL files in the 2 folders you mentionned.
Also, when I create it, it shows in SQL instance, but I right click on it to run a connection test and then I receive the error message 7302.

Thanks and Regards!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-09 : 07:04:57

I am seeing the same behavior, and I don't know what the solution is. If you do find out, please post back?

infodemers
Posting Yak Master

183 Posts

Posted - 2012-11-13 : 14:05:32

FYI

I unchecked the "Allow inprocess" option in the Microsoft.ACE.OLEDB.12.0 provider and all is well now.

So it was not a good idea to set 'AllowInProcess' to 1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-13 : 14:15:21

Very interesting - I have to resurrect my test code and see if that fixes it for me. Thanks for posting back.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources