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, for
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 needed
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
Tony
Hi 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.
================================================== ====

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

Having problems with SQLXML bulk import using diffgram

I have a program that writes out xml files that I use to import into SQL
server. When I start importing, most of the files that I have successfully
import, but some of them error out with the message: "SQLOLEDB Error
Description: Empty update, no updatable rows found Transaction aborted".
I've looked at the xml diffgram and there are definitely modified and
inserted rows in it. Has anyone run across this? I'd be glad to post the
diffgram here if anyone would like to see it.
Thanks,
Rob Baber
Do you use the diffgram or updategram format?
Sending a repro would help.
Thanks
Michael
"Rob Baber" <Rob Baber@.discussions.microsoft.com> wrote in message
news:E10640A7-BCE5-42AE-B842-C9956E067812@.microsoft.com...
>I have a program that writes out xml files that I use to import into SQL
> server. When I start importing, most of the files that I have
> successfully
> import, but some of them error out with the message: "SQLOLEDB Error
> Description: Empty update, no updatable rows found Transaction aborted".
> I've looked at the xml diffgram and there are definitely modified and
> inserted rows in it. Has anyone run across this? I'd be glad to post the
> diffgram here if anyone would like to see it.
> Thanks,
> Rob Baber
|||Could you please check the generated T-SQL by the SqlXml by using the
profiler? You are most likely using a diffgram behaving different than your
expectation or hitting a bug in SqlXml.
If you check out the sql and it seems like a wrongly generated one, could
you please post your diffgram and t-sql so that we can investigate if it is
a bug.
Thanks
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Baber" <Rob Baber@.discussions.microsoft.com> wrote in message
news:E10640A7-BCE5-42AE-B842-C9956E067812@.microsoft.com...
>I have a program that writes out xml files that I use to import into SQL
> server. When I start importing, most of the files that I have
> successfully
> import, but some of them error out with the message: "SQLOLEDB Error
> Description: Empty update, no updatable rows found Transaction aborted".
> I've looked at the xml diffgram and there are definitely modified and
> inserted rows in it. Has anyone run across this? I'd be glad to post the
> diffgram here if anyone would like to see it.
> Thanks,
> Rob Baber
sql

Having problems with SQLXML bulk import using diffgram

I have a program that writes out xml files that I use to import into SQL
server. When I start importing, most of the files that I have successfully
import, but some of them error out with the message: "SQLOLEDB Error
Description: Empty update, no updatable rows found Transaction aborted".
I've looked at the xml diffgram and there are definitely modified and
inserted rows in it. Has anyone run across this? I'd be glad to post the
diffgram here if anyone would like to see it.
Thanks,
Rob BaberDo you use the diffgram or updategram format?
Sending a repro would help.
Thanks
Michael
"Rob Baber" <Rob Baber@.discussions.microsoft.com> wrote in message
news:E10640A7-BCE5-42AE-B842-C9956E067812@.microsoft.com...
>I have a program that writes out xml files that I use to import into SQL
> server. When I start importing, most of the files that I have
> successfully
> import, but some of them error out with the message: "SQLOLEDB Error
> Description: Empty update, no updatable rows found Transaction aborted".
> I've looked at the xml diffgram and there are definitely modified and
> inserted rows in it. Has anyone run across this? I'd be glad to post the
> diffgram here if anyone would like to see it.
> Thanks,
> Rob Baber|||Could you please check the generated T-SQL by the SqlXml by using the
profiler? You are most likely using a diffgram behaving different than your
expectation or hitting a bug in SqlXml.
If you check out the sql and it seems like a wrongly generated one, could
you please post your diffgram and t-sql so that we can investigate if it is
a bug.
Thanks
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Baber" <Rob Baber@.discussions.microsoft.com> wrote in message
news:E10640A7-BCE5-42AE-B842-C9956E067812@.microsoft.com...
>I have a program that writes out xml files that I use to import into SQL
> server. When I start importing, most of the files that I have
> successfully
> import, but some of them error out with the message: "SQLOLEDB Error
> Description: Empty update, no updatable rows found Transaction aborted".
> I've looked at the xml diffgram and there are definitely modified and
> inserted rows in it. Has anyone run across this? I'd be glad to post the
> diffgram here if anyone would like to see it.
> Thanks,
> Rob Baber

have a problem

Who knows how to fix this..
who knows how to fix this
> MMC cannot open the file C:\Program Files\Microsoft SQL
Server\80\Tools\BINN\Sql Server Enterprise Manager.MSC.
This may be because the file doesnt exist, is not an MMC console, or
was created by a later version of MMC. This may also be because
Thx
Vegita
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message412335.htmlHi Vegita.
The error indicates one of a few things is the problem - have you checked
each of them? Can you offer us anything more to work with, such as:
(a) Does the file actually exist?
(b) Have you upgraded MMC.exe? (added new service packs lately etc)
(c) Have you uninstalled the SQL client tools perhaps?
A simple catch-all approach to solving the problem might be to simply
re-install the SQL Server Client Tools (not the server) which will probably
get you out of jail..
Regards,
Greg Linwood
SQL Server MVP
"Vegita" <Vegita.11xyqq@.mail.mcse.ms> wrote in message
news:Vegita.11xyqq@.mail.mcse.ms...
> Who knows how to fix this..
> who knows how to fix this
> Server\80\Tools\BINN\Sql Server Enterprise Manager.MSC.
> This may be because the file doesnt exist, is not an MMC console, or
> was created by a later version of MMC. This may also be because
> Thx
>
> Vegita
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message412335.html
>