none
How to create linked server to Access 2007 accdb file? RRS feed

  • 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?

    Wednesday, February 20, 2008 12:47 PM

Answers

  • 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




    • Proposed as answer by hoffmanc Sunday, January 11, 2009 2:55 AM
    • Marked as answer by Naomi NModerator Friday, February 18, 2011 4:46 AM
    Wednesday, June 11, 2008 2:01 PM
  • 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

     

    Thursday, May 8, 2008 5:56 AM
    Answerer

All replies

  • 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

     

    Thursday, May 8, 2008 5:56 AM
    Answerer
  • 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.

     

    Monday, May 26, 2008 8:29 PM
  • 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?

    Thursday, May 29, 2008 11:52 AM
  • 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




    • Proposed as answer by hoffmanc Sunday, January 11, 2009 2:55 AM
    • Marked as answer by Naomi NModerator Friday, February 18, 2011 4:46 AM
    Wednesday, June 11, 2008 2:01 PM
  • @rmtuser = N'Your Linked Server Name',

    can be replaced with

    @rmtuser = @@servername, 


    Awesome job!
    Sunday, January 11, 2009 2:55 AM
  • 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
    Friday, March 6, 2009 9:51 PM
  • 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
    Wednesday, April 29, 2009 5:46 PM
  • 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
    Friday, September 18, 2009 5:19 AM
  • 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
    Thursday, October 15, 2009 10:05 PM
  • 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
    Tuesday, November 3, 2009 10:47 PM
  • 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
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

    Saturday, November 21, 2009 4:48 PM
    Moderator
  • I just got this working on SQL Server 2005 express edition.
    Added a key (but I'm not sure if this is what made it work) ..
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.Jet.OLEDB.4.0\DisallowAdhocAccess

    value 0
    Monday, January 4, 2010 5:04 AM
  • I came across Jose Alve's ("IID_IDBCreateCommand") error messages when trying to use SQLServer Express 2005 SP3 and dynamic queries using OPENDATASOURCE()  SQLServer Express 2005 does not have a link to the ACE Driver in the registry where it looks for that connection (and security settings).

    It Can be done for SQLServer Express 2005 without installing the data connectivity components (so long as office 2007 is installed)

    I found the solution for that error here:
    http://www.integralwebsolutions.co.za/Blog/EntryId/283/Importing-and-using-Excel-data-into-MS-SQL-database.aspx

    Short Answer for fixing SQLServer Express 2005:
    Enter the registry info below or run a reg file with the following info

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0]
    "AllowInProcess"=dword:00000001
    "DynamicParameters"=dword:00000001

    Restart SQL Server Services

    Rod Nurk's solution above may help with SQL Server 2005 Express and SQL Server 2005 when using the Jet 4.0 Driver (at least initial releases).  I saw a bug report that people had to add that key for the provider to allow ad hoc access even if it was opened up system wide by the surface are configuration tools.  It may have been fixed in further Service Packs.  Dunno.  I was querying fine with Jet 4.0 with an excel file on SQLE 2005 SP3 and it did not have that registry key present.

    Note:  I also came across OPENDATASOURCE remaining with a handle open to the file when failing on the Microsoft.ACE.OLEDB.12.0 Provider and giving the IID_IDBCreateCommand error.  If that happens you have to find a way to release the handle before anything can work. logout, reboot, restart sql server, etc.  Until the file is released, If the provider is present and configured, the server will return the (null) provider error.

    • Proposed as answer by lespaa Wednesday, February 10, 2010 7:23 PM
    Wednesday, February 10, 2010 7:22 PM
  • I used the script below difying only linked server name and db path, but I'm still getting the 7339 error when trying to list the tables in SSMS. Any clues?

    ****** Here's the error

    Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TT_Access" reported an error. Access denied.

    Msg 7301, Level 16, State 2, Procedure sp_tables_ex, Line 41

    Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TT_Access".

    **************Here's the script
    -- SQL Server 2008 CREATE Linked Server script

    EXEC master.dbo.sp_addlinkedserver @server = N'TT_Access', @srvproduct=N'ACCESS 2007', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'H:\TT\TT.accdb'

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

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

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

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

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

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

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

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

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

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

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

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

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

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

     

    Thursday, February 17, 2011 4:34 PM
  • Yup, AllowInProcess did it for me, tx
    Wednesday, October 5, 2011 3:30 PM
  • thanks! thanks!
    Wednesday, July 31, 2013 5:56 AM