Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Lancashire, UK
    Posts
    33

    Angry Unanswered: Creating a Linked Server to Access DB

    I'm trying to create a linked server to an Access database that resides on a separate machine.

    On my PC, I can create a link to the Access db, and view, update, add and delete data. If I create the same linked server on our production Server, I can again view, update, add and delete data. All's well so far.

    If I now go back to my own PC (used for developing) I cannot access the linked server on the production machine.

    The Access database is stored on a separate PC, so the I'm linking to a remote db. As I said this works fine if I'm sat in front of the PC that the linked server is created on - but not if I use a client PC to connect.

    I create the linked server using the following command:
    exec sp_addlinkedserver
    @server = 'AccLinkedServer',
    @provider = 'Microsoft.jet.OLEDB.4.0',
    @srvproduct = 'OLE DB Provider for Jet',
    @datasrc = '\\DatabaseServer\AccessDatabase.mdb'


    If I run the SQL statement:

    SELECT * FROM AccLinkedServer...AnyTableYouLike

    I get this error:
    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
    [OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\\DatabaseServer\AccessDatabase.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
    OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

    I've searched in the MS knowledge base, and this forum and followed all of the advice that was available, but it still won't work.

    Anyone got any other advice, before I go totally insane with this.

    Cheers.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    But the linked server is registered with the sql server instance...

    This doesn't make sense....are you sure you're pointing to the same instance...

    can you see the linked server in EM?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Location
    Lancashire, UK
    Posts
    33
    Originally posted by Brett Kaiser
    But the linked server is registered with the sql server instance...

    This doesn't make sense....are you sure you're pointing to the same instance...

    can you see the linked server in EM?
    A bit more detail may help explain. We have a server (MainServer) which runs SBS and SQL. We have another PC which we use as a server (DatabaseServer).

    I'm developing on my PC (DevelopmentPC) with a copy of SQL Developer and VB6.

    I have created on my PC a linked server (AccLinkedServer) to the DatabaseServer, which allows me full access to this.

    I can also create a linked server (AccLinkedServer) on the MainServer to the same data, and also have full access to the data from the MainServer.

    If I try to run the command select * from AccLinkedServer...tblDataTable (where AccLinkedServer is the MainServer version) from Query Analyser on my PC I get the error shown in my original post.

    I would like to be able to access the data we store on DatabaseServer from within SQL/VB - it would save a lot of time for people.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •