If I do the following, it works...

EXEC sp_addlinkedserver @server = test ,
@srvproduct = 'Jet 4.0',
@provider = 'Microsoft.Jet.OLEDB.4.0',
-- @datasrc = 'i:\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 = 'i:\test Database\Data\test.mdb',
@datasrc = 'd:apps\test Database\Data\test.mdb',

to

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

where I: 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 'i:\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

I haven't played with linked servers with Access for a while, but if I
recall correctly, you don't need to supply security information for
unsecured Jet databases. If your Jet database is secured, then
creating a linked server is a giant PITA that rarely works out. In an
unsecured Jet database, everyone logs on silently as the Admin user
with a blank password (empty string), which is why @useself = TRUE
didn't work (it's trying to pass your Windows or SQLS login
credentials, and since Jet doesn't support integrated security, that's
a non-starter). So try just running sp_addlinkedserver and omit
sp_addlinkedsrvlogin and see if that works.

-- Mary
MCW Technologies
http://www.mcwtech.com

On Wed, 22 Oct 2003 11:59:47 +0930, "Jag Kitchens"
<jagkitchens@chariot.net.au> wrote:

>If I do the following, it works...
>
> EXEC sp_addlinkedserver @server = test ,
> @srvproduct = 'Jet 4.0',
> @provider = 'Microsoft.Jet.OLEDB.4.0',
> -- @datasrc = 'i:\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 = 'i:\test Database\Data\test.mdb',
> @datasrc = 'd:apps\test Database\Data\test.mdb',
>
>to
>
> @datasrc = 'i:\test Database\Data\test.mdb',
>-- @datasrc = 'd:apps\test Database\Data\test.mdb',
>
>where I: 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 'i:\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
>
>

Nope if I remove sp_addlinkedsrvlogin code, I get

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

and no data access...

Any more Ideas?