Having trouble Creating Linked Server to Access database

I been having an issue trying to connect an Access database through SQL
Server as a Linked Server.
The crux of the problem is the Access Database (mdb file) has a Database
Password and this cannot be removed due to the database still being used by
a
VB application. SO can someone Assist in a detailed outline that works, fo
r
configuring a Linked Server connecting to a mdb with a Database Password set
.
If the password is temperarily removed from the database I am able to create
a Linked Server using the "MS Jet 4.0 OLD DB Provider" However to complete
my objective I cannot remove the Database Password on our clients DB.
When the password is put back in, then once again I am unable to access the
DB. I have tried different configuration options and recieved different
errors. for instance on one try SQL Server was erroring and saying it neede
d
a mdw file that we do not have becaue that is for User Level security which
out MDB is not using.
I tried using a OpenROWSET command and providing a Provider Connection
String setting the Database Password in the String, However this Also errors
with a Could not find Installable ISAM.
Thanks
TonyHi Tony,
I understand that you would like to know how to open your
password-protected access database (.mdb) in SQL Server via linked server
or OPENROWSET/OPENDATASOURCE.
If I have misunderstood, please let me know.
I reproduced your issue at my side. It seems not possible to use linked
server or OPENROWSET to connect to a password-protected mdb file. However
per my test, you can use OPENDATASOURCE to query your MDB file with
specifying the parameter "Jet OLEDB:Database Password=******". For example:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source="C:\Documents and Settings\charles\My Documents\db1.mdb";User
ID=Admin;Password=;Jet OLEDB:Database Password=myPassword!')...tblDT;
I am not sure why the parameter does not work for OPENROWSET and linked
server and I will try to consult the product team regarding the issue. If
there is any response, I will let you know. Anyway as a temporary
workaround, I recommend that you use OPENDATASOURCE to see if it helps.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Tony,
Just notify that I have not got the product team's confirmation now. I will
try to follow up them. Since the process may need a long time, could you
please just leave me (changliw_at_microsoft_dot_com) an email response so
that I can timely update you when I got the confirmation?
Also could you please let me know whether or not the OPENDATASOURCE method
worked for you?
If you have any questions or concerns, please feel free to let me know. It
is my pleasure to be of your assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============sql