Can not merge word document from SQL via Access (Word2002 / Access2002 / SQL2000)

[S?ren Thygesen Holm]

Hey,

I am trying to merge a word 2002 document with a datasource, which is
a query in an Access-2002 database.

This query is an extraction of data selected from an Access-table and
several MS-SQL tabels linked to the Access-database with an ODBC DSN.

If I use 'trusted connection' in my ODBC connection, the merge works
perfectly.
If I use 'not trusted connedtion' it dos'nt work.

I am not prompted for a password - and the datasource is not found.

There is no problem when I open the Access-database and run the same
query.
I am prompted for a password, and the result is shown.

This is a problem for me, as the users are not loged on to the domain
where the MS-SQL 2000 database is placed.

best regards
S. T. Holm

 

[Peter Jamieson]

Obviously if you are not using Integrated Security/Trusted connection, then
something is going to have to supply the username/password details. So
either they have to be stored somewhere, or something is going to have to
ask for them.

As far as I know, Word is never going to ask you for any credentials unless
they are required to open its "primary" data source (in this case, the
Access/Jet database). And even then, it may not correctly handle whatever
information you may provide. So either
a. Word has to open Access in a way that allows Access to ask for the
credentials or
b. Access has to store the credentials or
c. Word has to store the credentials and provide them to Access/Jet in a
way that does what you need.

However, at that point there are enough layers that I'd have to go and check
what works myself, so can only make the following suggestions:
a. if you check Word Tools|Options|General|"Confirm conversions at open",
go through the connection process to the Access query again, and select the
DDE option when prompted, Word should open Access wehn it needs the data,
and that /may/ result in Access prompting you for the access credentials.
And be careful, because the dialog box Access displays may be hidden by Word
(whose title bar should start flashing in that case) so you might need to
use Alt-tab to switch to Access to enter the credentials. Also, you have al
the problems associated with cranking up the Access application just to get
the data.
b. it may be possible to store the credentials in the Access Query code (as
far as I know, you cannot store them in an ODBC machine (User or System)
DSN, but you may be able to store them in a File DSN). Even if it is
possible, it is not desirable if the credentials are not public domain.
c. it may be possibel to store the credentials in Word or one of its
support files. But I don't think so, and in any case, you have the same
problem as in (b).