locked
SQL Server 2005 Linked Server to Access ACCDB RRS feed

  • Question

  • I want to create a linked server to my large accdb back end database. It's in a folder that's protected, but I'm allowed. Using SSMS and my SS2005 SP1 database, I'm setting up a linked server as follows:

     

    exec sp_addlinkedserver 
    	@server= CPAS_BE,
    	@srvproduct=N'cpas_be',
    	@provider=N'Microsoft.ACE.OLEDB.12.0',
    	@datasrc=N'\\WINNTDOM\root\common\xxx\secure\Construction\Access\XXX\Backend\Composite_BE.accdb'
    

    I've read a series of ways to set up a login, none of which have worked. I suspect that the SQL Server execution account does not have permission to the folder where my accdb is located. I get either error 7399 or 7303, depending on the login. I've checked "Allow Inprocess" for the Access 12 OLEDB driver.

     

    I have been able to reference this db from this server using C#, and SSIS.

    Anyone have a login I can create to get this done?


    Jim
    • Edited by JimS-Indy Wednesday, July 6, 2011 2:49 PM spelling
    Wednesday, July 6, 2011 1:29 PM

Answers

  • jsoldo81 -- I did get it linked by running SSMS in administrator mode (I think I right-clicked the link to it and clicked on "run as administrator....") This was not entirely satisfactory, but works in a pinch.

     

    Hope this helps


    Jim
    • Marked as answer by JimS-Indy Friday, November 4, 2011 2:09 PM
    Friday, November 4, 2011 2:09 PM

All replies

  • It will help if you post the complete error, as 7399 represents multiple errors and for 7303 error you may check https://connect.microsoft.com/SQLServer/feedback/details/587897/connecting-via-a-linked-server-to-an-access-2010-database-file connect link.

    For 7399 error I see that it may happen because the login account does not have full access to the temporary folder of the SQL Server startup account. The linked server query runs in the context of the login account. If you run a linked server query, SQL Server tries to create a temporary file data source name (DSN) in the temporary folder of the SQL Server startup account.


    Satya SKJ, SQL Server MVP [Knowledge Sharing Network - www.sqlserver-qa.net]
    Author of SQL Server 2008 R2 Administration cookbook.
    Follow me @sqlmaster.
    Wednesday, July 6, 2011 1:43 PM
  • Here's the 7303 error (I copied the accdb to a different network folder, though still protected):

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    "The linked server has been created but failed a connection test. Do you want to keep the linked server?"

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ACCESS".
    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ACCESS" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)

     


    I moved the accdb file to the server's C: drive, but get the same error.

     

    Jim

    • Edited by JimS-Indy Wednesday, July 6, 2011 2:35 PM add'l info
    Wednesday, July 6, 2011 2:28 PM
  • please make sure that the appropriate data connectivity or providers are installed

    for office 2007 please see this

    http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23734


    http://joeydj.com
    Thursday, July 7, 2011 1:03 AM
  • Access Database Engine is installed. As I said, SSIS works.
    Jim
    Thursday, July 7, 2011 1:56 AM
  • Any solution to this yet?  I am in the exact same boat at JimS >;(
    Thursday, August 4, 2011 4:41 PM
  • jsoldo81 -- I did get it linked by running SSMS in administrator mode (I think I right-clicked the link to it and clicked on "run as administrator....") This was not entirely satisfactory, but works in a pinch.

     

    Hope this helps


    Jim
    • Marked as answer by JimS-Indy Friday, November 4, 2011 2:09 PM
    Friday, November 4, 2011 2:09 PM