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