sql linkedserver to access 2k database with database password

[paul a via AccessMonster.com]

greetings from a database newbie...

my other office uses msaccess and i get daily updated msaccess 2k .mdb file.

i, however, have mssql backend and msaccess frontend.

i want to create a linkedserver within mssql to the msaccess .mdb file, but
haven't figured out
how to supply the msaccess database password to the addlinkedserver
storedprocedure.

i can remove the password daily, but i figure this task is better left to
scripting/programming.

can anyone help?

 

[giorgio rancati]

Hi Paul,
----
EXEC sp_addlinkedserver
@server = 'test_Access',
@provider = 'MSDASQL',
@srvproduct = 'Microsoft Access',
@provstr = 'DRIVER=Microsoft Access Driver
(*.mdb);DBQ=C:\Data\db1.mdb;PWD=YourPwd'