Problem with linked server from SQL Server to Access 2003 database

[NigelA]

I have set up the linked server OK but it is failing to connect properly.
Anyone know what I need to do to fix this?

I have also posted this in sqlserver.programming where someone suggested I
post it in an Access newsgroup. I tried the multi user group but it seems to
be very quiet in there.

Scripts and precise error message below.

EXEC sp_addlinkedserver
@server = N'MyAccessDB',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'Access',
@datasrc = N'\\mynetwork\dfs\CallCentre\Germany\Departments\Hotline\'
GO
-- Runs OK

-- Set up login mapping
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N'MyAccessDB',
@useself = N'false',
@locallogin = NULL,
@rmtuser = N'operations',
@rmtpassword = 'MyAccess'
GO
-- Runs OK

-- List the tables on the linked server
EXEC sp_tables_ex N'MyAccessDB'
GO

-- Gives message
--OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyAccessDB"
returned message "Cannot start your application. The workgroup information
file is missing or opened exclusively by another user.".
--Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41
--The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
"MyAccessDB" reported an error. Authentication failed.
--Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
--Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "MyAccessDB".

Notes:

The access db is definately not opened exclusively by another user as
I can open it OK via Access itself using the same credentials.

The database does not have a MDW file associated with it.

The database resides on a network share and the users of it connect to it
via terminal server sessions.

TIA
Nigel Ainscoe

 

[Stefan Hoffmann]

hi Nigel,

I have set up the linked server OK but it is failing to connect properly.
Anyone know what I need to do to fix this?

http://msdn2.microsoft.com/en-us/library/ms175866.aspx

Read the part about secured databases.

mfG
--> stefan <--

 

[NigelA]

I'll give that a try, but I can't find an MDW file to point to at this time