:( Does anyone have an examples of how to link a password protected MSAccess database into SQL Server 2000 please?

I've tried using the following stored procedures:
sp_addlinkedserver
sp_addlinkedsrvlogin

The actual link is created fine, but selecting the Tables produces:
 

The actual parameters used for the stored procedures are:
sp_addlinkedserver:
@server='TestDB'
@provider='Microsoft.Jet.OLEDB.4.0'

(I've then tried @datasrc or @provstr separately)
@datasrc='c:\test.mdb'
@provstr='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.mdb;Jet OLEDB:Database Password=xyz'

sp_addlinkedsrvlogin:
@rmtsrvname='TestDB'
@useself='False'
@locallogin=Null
@rmtuser='Admin'
@rmtpassword='xyz'

If anyone can shed any light on this matter or show me what I'm doing wrong, would be very grateful.

Thanks in advance.
Carl

http://support.microsoft.com/default.aspx?scid=285833

Thanks for the link. Have tried these and various other combinations and still not having any luck.

1. Do I need to use a .mdw file (currently not using one as I don't need user level security)
2. As the default user in MSAccess is 'Admin', does there need to be an 'Admin' user in Logins associated with Linked Servers. (Have set one up but no apparent effect).
3. With sp_addlinkedsrvlogin I've tried the following values for @locallogin: Admin, sa, Null
4. @rmtpassword was set to Null

I have proved that the linked server can work, by taking the database password off the MSAccess database. As soon as I put the password back on I get the Error 7399 again.

I have also proved that using a password with the 'Admin' user (in MSAccess database) and not using a database password does work if the registry key does point to system.mdw (but this isn't what I was after).

Any addition advice? I can't see the woods for the trees at the moment.

Thanks,
Carl

Further to my last reply ...
If I remove the database password from the MSAccess database, then add a password to the 'Admin' user and use the examples from the Microsoft link, it does work.
Unfortunately this isn't what I'm after as I need to retain the database password on the MSAccess database.

If anyone had this problem and was using the Search and found this thread, here is my work-around.
I don't actually link the MDB but using T-SQL try the following:

  I've also tried the Data Source as a network path and that also works. The only thing I can't confirm is the Jet drivers on the same box as the SQL Server installation.

I tried your workaround and got the following

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Regards,

Sean Anderson