How to create linked server to Access 2007 accdb file?

Question

How does one create a linked server to an Access 2007 (accdb) file? I am aware that to create one to a mdb file, the code would be -

EXEC sp_addlinkedserver 
   @server = 'SEATTLE Mktg', 
   @provider = 'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
GO

What is the corresponding code for Access 2007 accdb files?

Answer 1

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ACCESS', @srvproduct=N'access', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'f:\test.accdb'
GO

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

 

Answer 2

I'm getting error 7399 when I try to browse the tables in SQL Server Management Studio (sql server 2008).  I setup the linked server via the gui.  Do I put "access" under product name? 

 

p.s  jet 4.0 oledb provider works fine in linked server to mdbs.

 

Answer 3

i've followed all those steps but it keeps giving me an error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "EAFILE" reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "EAFILE".


I have SP2 installed... Any ideas?

Answer 4

HERE IS THE SOLUTION FOR ALL OF YOU WITH THE SAME PROBLEM:

- Install the 2007 Office System Driver: Data Connectivity Components on your server.
here is the link:
http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

- Make shure you have SQL SERVER 2005 Standard installed. IT DOESN'T WORK'S ON SQL EXPRESS. No mather what you do. It only works on the standard versions...

- Install SQL SERVER SP2

- On your Server Objects > Linked Servers > Providers, Righ Click on Microsoft.ACE.OLEDB.12.0 and select Options. Check the option Allow inprocess

- Use this T-SQL template (replacing by your own values) to create a connecto to the Linked Server:

Code Snippet

-- ==============================================
-- Add Linked Server Access 2007 ACCDB template
-- BY JOSÉ ALVES @ CCG - UMINHO - PORTUGAL
-- ==============================================

EXEC sp_addlinkedserver
    @server = N'Your Linked Server Name',
    @provider = N'Microsoft.ACE.OLEDB.12.0',
    @srvproduct = N'Access2007',
    @datasrc = N'C:\path\to\your\db.accdb'
GO

-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = N'Your Linked Server Name',
    @useself = N'TRUE',
    @locallogin = NULL,
    @rmtuser = N'Your Linked Server Name',
    @rmtpassword = ''
GO

-- List the tables on the linked server
EXEC sp_tables_ex N'Your Linked Server Name'
GO

-- Select all the rows from table1
SELECT * FROM [Your Linked Server Name]...table1

I hope this helps you. To me it was a big headache but i figured out after many tries...

Greetings from Portugal

Answer 5

@rmtuser = N'Your Linked Server Name',

can be replaced with

@rmtuser = @@servername,

Awesome job!

Answer 6

Jose

Thank you for your wonderful post inrelation to a linked server to an access 2007 database. You mentioned that this was not possible with SQL express 2005. Does it work with SQL express 2008?

Thanks in advance for any help.

Neil

Answer 7

I'm getting the same 7399 error when using Microsoft Access 2007 as as Linked Server in SQL Express 2008.

Please tell me there's a way to get this to work in SQL Express??

Greatly appreciate any input that can be provided!  Thanks you -

Ryan D. Hatch

Answer 8

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ACCESS', @srvproduct=N'access', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'f:\test.accdb'
GO

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

 

thanx
it works

Answer 9

This worked GREAT for me and saved me TONS of time!  Thank you SO much!

I have one question....  When I open the linked server in the management studio, I don't see any tables listed.  Keep in mind that in this scenario, I'm using Access 2007 as a pass-through agent to other databases that have proven difficult to access directly by SQL Server.  So my access db contains a series of linked tables to other external databases (non-sql).

Using your example query above, I am clearly able to access all of the tables.  But I'm wondering if there is some trick to get the tables to be listed in the management studio GUI to make enumerating/finding things a little easier.

I can't emphesize enough how much time you just saved me!  I had previously been under the impression that in order for the linked external tables to be visible to SQL I would have to create access queries, which do seem more easily visible in the management studio UI's.  I was setting down to create hundreds of those, but no more!

I look forward to your reply!

Thanks!
Toby Hosterman

Answer 10

Hello, I have same issue, I wanted to create linked server to Access 2007.  Can this be possible on SQL Server 2005 Developer Edition with SP3 instance Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Microsoft Corporation Developer Edition on Windows NT 6.0 (Build 6001: Service Pack 1)  I have tried all above suggestions but still i am getting below error OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ACCESS" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ACCESS". Please help!!! Thanks in advance Sunny

Answer 11

The following script creates a linked server (to Access 2007 db) on SQL Server 2008.

In SSMS Object Explorer there is a tree view of tables and views in the Access database. Automatic scripts can be created for example to SELECT from tables/views by right clicking on the object.

Let us know if works for you.

-- SQL Server 2008 CREATE Linked Server script

EXEC master.dbo.sp_addlinkedserver @server = N'LINKED_ACCESS_2007', @srvproduct=N'ACCESS 2007', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'F:\data\Access2007\Northwind2007.accdb'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LINKED_ACCESS_2007',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

EXEC master.dbo.sp_serveroption @server=N'LINKED_ACCESS_2007', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'LINKED_ACCESS_2007', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'LINKED_ACCESS_2007', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'LINKED_ACCESS_2007', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'LINKED_ACCESS_2007', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'LINKED_ACCESS_2007', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'LINKED_ACCESS_2007', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'LINKED_ACCESS_2007', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'LINKED_ACCESS_2007', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'LINKED_ACCESS_2007', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'LINKED_ACCESS_2007', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'LINKED_ACCESS_2007', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'LINKED_ACCESS_2007', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com