1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Linked Server from SQL Server 2008 to Access 2000 db gives error

Discussion in 'Business Applications' started by lkelly, May 12, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. lkelly

    lkelly Thread Starter

    Joined:
    May 12, 2011
    Messages:
    4
    I have a Access 2000 database residing on one sever and an installation of SQL Server 2008 residing on another. Both servers are running Windows Server 2003. I created a linked server using the code below. When I run EXEC sp_tables_ex 'PropCont' I get the following,
    "OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
    Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error."

    I tried changing the code to specify my account information, which has Administrative privileges on the domain and I get,
    "OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
    Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed."

    Originally I was getting an error that said, "The workgroup information file is missing or opened exclusively by another user" I found an article on line that suggested I do the following,
    "1. Open SSMS
    2. Expand Server Objects, Linked Servers, Providers
    3. Double click the provider (Microsoft.Jet.OLEDB.4.0)
    4. UNTICK the "Allow inprocess" option"
    I did this and I stopped getting that error but started getting the error I listed above however when I went back and checked the "Allow inprocess" option was still checked. I can't get it to remain unchecked. I can't find anything regarding how to specify the MDW file which is located in the same folder as the database file.

    Another article suggested making sure it is checked and also checking "Nested Queries". This had no effect.

    I believe the linked server has been created correctly as I can see it and it's properties in SSMS and it shows up in the list of linked servers when I run, EXEC sp_helplinkedsrvlogin

    The Access database has been in use for years. Users access it through the Access runtime.

    Any suggestions would be greatly appreciated.

    EXEC sp_addlinkedserver
    @server = 'PropCont',
    @provider = 'Microsoft.Jet.OLEDB.4.0',
    @srvproduct = 'Access',
    @datasrc = '\<Server Name>SharedDatabasesPropCont.mdb'
    GO

    EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'PropCont',
    @useself = 'false',
    @rmtuser = 'Admin',
    @rmtpassword = ''
    GO
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Is there an ODBC connection set up on a server that is managing the communication between Access and SQL? I would think it would be residing on the server where the Access db is sitting.
     
  3. lkelly

    lkelly Thread Starter

    Joined:
    May 12, 2011
    Messages:
    4
    Hello Rockn,

    And thanks for your reply. There is not a ODBC connection set up on either server. If I were to create on how would I have to change my code to utilize it.
     
  4. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    If it has worked fine up until recently there is no reason to change the way you are doing things. Was there a Windows update or something that happened to coincide with the stored procedure not functioning any longer?

    By the way, what account is being used to access the database? Does it have the appropriate rights? Have any passwords changed for any serviec accounts or the account used in that SP?
     
  5. lkelly

    lkelly Thread Starter

    Joined:
    May 12, 2011
    Messages:
    4
    Everything is still working as it has. The users who have used the database through the Access runtime still can. What I'm trying to do now is access the same database through a SQL Server 2008 linked server.

    I've tried using the Admin account as well as my own which has full permissions.
     
  6. lkelly

    lkelly Thread Starter

    Joined:
    May 12, 2011
    Messages:
    4
    In may initial post I had said that I was working with SQL Server 2008 but that is on my development machine. On the server itself SQL Server 2000 is installed.

    I have tried following the instructions in this article, http://support.microsoft.com/kb/246255
    On the server that contains the installation of SQL Server I made the registry change suggested in the article so that the given key value points to the System.MDW file that is on the server where the mdb file is.

    If anyone has any ideas I'd love to hear them.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/996428

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice