If I do the following, it works...

EXEC sp_addlinkedserver @server = test ,
@srvproduct = 'Jet 4.0',
@provider = 'Microsoft.Jet.OLEDB.4.0',
-- @datasrc= '\\computer\test Database\Data\test.mdb',
@datasrc= 'd:apps\test Database\Data\test.mdb',
@location = NULL,
@provstr = NULL,
@catalog = NULL
GO

--Set up login mappings

EXEC sp_addlinkedsrvlogin @rmtsrvname = test,
@useself = FALSE,
@locallogin = NULL,
@rmtuser = admin,
@rmtpassword = NULL
GO

--Query one of the tables: file1#txt

SELECT *
FROM test...[tblcontacts]
GO

-- Drop server and releasing user
EXEC sp_dropserver test,'droplogins'
go

but if i change

-- @datasrc = '\\computer\test Database\Data\test.mdb',
@datasrc = 'd:apps\test Database\Data\test.mdb',

to

@datasrc = '\\computer\test Database\Data\test.mdb',
-- @datasrc = 'd:apps\test Database\Data\test.mdb',

where computer is a mapped path to a win 2000 computer
and test.mdb is the same database on both computers


I get the following:

Server: Msg 7399, Level 16, State 1, Procedure
sp_tables_ex, Line 20
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 '\\computer\test Database\Data\test.mdb'.
It is already opened
exclusively by another user, or you need permission to
view its data.]

if I change
sp_addlinkedsrvlogin
to
@useself = TRUE,

then

I get the message:

Server: Msg 7303, Level 16, State 2, Procedure
sp_tables_ex, Line 20
Could not initialize data source object of OLE DB provider
'Microsoft.Jet.OLEDB.4.0'.
[OLE/DB provider returned message: Not a valid account
name or password.]
Server: Msg 7303, Level 16, State 2, Line 8
Could not initialize data source object of OLE DB provider
'Microsoft.Jet.OLEDB.4.0'.
[OLE/DB provider returned message: Not a valid account
name or password.]


Any ideas?

Appreciated!

David Walker

[quoted text, click to view]

I have tried using UNC code, see original script
ie \\computer\apps\test.mdb
and i still get the same error.
(the "d" mapping is a local drive that works!)

- I have read a couple of articals that may suggest that
i change the default system.mdw file in the registry
(Knowledge base article Q246255)

Has anyone tried this and succeeded?
anyone got any other ideas?

David,

I'm having a very similar problem to this. I can create a
linked server and access the data on the machine that SQL
resides - but not remotely.

Like you, I have tried the variations on creating the
linked server, and added logins, with no noticeable
difference.

I would have thought that you would only need to change
the default system.mdw file if the database you were
trying to access was secured. Mine isn't.

I'll post again if I find out anymore.

Cheers

Steve

Since with a linked server, the SQL Server startup account is making the
connection. Using drive letter designations will not reliably work. It is
best to use UNC for access to remote shares.

Rand
This posting is provided "as is" with no warranties and confers no rights.

David,

I have been trying to sort out my own problem with this. I
changed the registry setting for the system.mdw file. it
made no difference.
[quoted text, click to view]