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"
[quoted text, click to view] <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
>
>
