none
Trying to add a linked server - SQL Server 2008 Express Edition to Access 2007

    Question

  • OS: Vista SP1
    SQL Server 2008 Express Edition installed locally
    Office 2007 installed locally

    I'm trying to add a linked server to a named instance of the SQL Server 2008 that links to an Access 2007 database.

    I've spent a couple of hours trying this now - many, many different ways.

    Always seem to end up back at Error 7303 - http://msdn.microsoft.com/en-us/library/aa226395(SQL.80).aspx

    I've tried all versions of Access database file 2000, 2002-2003, 2007
    I've tried to use different drivers -

    Microsoft.Jet.OLEDB.4.0
    ODBC
    Office 12 for Access driver

    I've tried to use the upsizing wizard from Access - no good, cannot get it to connect to the SQL Server 2008 Express Edition instance

    Whichever way I try it I end up with this message/error:

    the upsizing wizard only works with microsoft sql server versions 6.50 sp5 or higher

    I've read: http://support.microsoft.com/default.aspx/kb/838594 and followed it - same error.

    I really want this to just work. Like it should already.

    Any suggestions appreciated.

    Matt Haddon
    Friday, May 15, 2009 1:00 PM

All replies

  • A bit of an update as I've had no replies so far...

    SP1 for the SQL Server 2008 Express Edition was just installed.

    Problem remains.

    I am now only trying to use this driver:

    Microsoft.ACE.OLEDB.12.0

    I have noted that I can create a Linked Server that if I right click it from SSMS and then select 'Test Connection' it returns 'Succeeded'

    However.... when trying to expand the Linked server in the gui to see Tables or Views I get the following:




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

    Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

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

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

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

    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TEST" reported an error. Access denied.
    Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TEST". (Microsoft SQL Server, Error: 7399)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476



    As before any help would be greatly appreciated.

    Matt Haddon

    Friday, May 15, 2009 10:44 PM
  • One further point to note here is when the error dialog pops upin SSMS there is a 'show technical details' button. Clicking this button reveals more detail about what went wrong and it includes this:

    Error Number: 7399
    Severity: 16
    State: 1
    Procedure: sp_tables_ex
    Line Number: 41


    I have looked at the sp_tables_ex procedure but I'm kind of stuck here.

    To clarify how I managed to get past the 7303 error - it seems that if I select the properties of the driver (under Providers), if the 'Allo Inprocess' is ticked I get the 7303 error but if it's cleared I do not and instead I get the 7399 error when I try to view the tables.

    I've also tried changing the SQL Server user account credentials to various different accounts to no avail.

    Matt Haddon
    Friday, May 15, 2009 11:35 PM

  • Hello Matt


    Just to test if it is a permissions problem, copy the mdb file to a location like

    C:\Program Files\Microsoft SQL Server\...\MSSQL\Data and update sp_addlinkedserver to this new location.

    Thanks
    Sreeka

    Saturday, May 16, 2009 3:09 PM
  • Thankyou for the suggestion but this is still not working.

    I have moved the Access mdb file as suggested and my sp_addlinkedserver command looks like this:

    sp_addlinkedserver

     

    "TEST","MsAccess2k","Microsoft.Jet.OLEDB.4.0","C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\odbc.mdb"


    The linked server is created succesfully but is unusable as either the error 7303 occurs if 'Allow Inprocess' is checked against the Jet provider or 7399 occurs if 'Allow Inprocess' is checked.

    Thanks

    Matt Haddon

    Thursday, May 21, 2009 2:30 PM
  • Did you ever solve this - I have a similar problem!
    Monday, April 11, 2011 8:51 AM
  • I am can Link a server to another SQL Server instance on another server and drill into it up to the DB but the tables of the DB are blocked.

    I will poke around and research.  If I find anything out, will let you folks know.  Really don't think it is a permissions issue.

    --Murphy

    Friday, September 30, 2011 6:54 PM
  • Did you ever find a solution to this issue? I am having a very similar issue and can't seem to find a solution?
    Monday, January 09, 2012 11:24 PM
  • Had the same issue (first 7303 but" allow inprocess" solves it , then 7399). This works for me:

    http://www.qdeveloper.com.au/forum.php?homeinclude=topicdetails&forum_id=1&category_id=45&post_id=3568

    error 7399:"This problem occurs 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." - from http://support.microsoft.com/kb/814398

    I thin its cause I installed server withoit option "Run as admin", or didnt choose account for server in instalation process.

    Thursday, April 26, 2012 1:05 PM
  • hi, could you refer the provide forum to you as it may helpful for the same.

    http://www.dotnetspark.com/links/67216-sql-server-2008----ms-access-2003-linked.aspx

    Friday, May 04, 2012 12:07 PM