Microsoft ADP / Word Automation

[John Kounis]

I recently converted from a .mdb file to a .adp front-end for a backend
running MSDE. There have been dramatic increases in performance, and I'm
happy with the conversion. However, one feature of the database--Microsoft
Word 2000 Automation-- is causing headaches.

The old version of the software had a line as follows:

wrdDoc.MailMerge.OpenDataSource Name:=CurrentProject.FullName,
LinkToSource:=True, Connection:="QUERY <my query name>"

Now that I have converted to an adp file, I apparently cannot use
"Name:=<filename>.adp" as a data source, so I tried:

wrdDoc.MailMerge.OpenDataSource Name:="", LinkToSource:="True", _
Connection:="Provider=SQLOLEDB;Persist Security Info=True;Data
Source=<SERVER>;User ID=<UID>;Password=<PWD>;Initial Catalog=<dbname>;" _
SQL_Statement:="SELECT * FROM <myviewname>"

Apparently, the above won't work on Word 2000. I think it's because Word
2000 cannot use OLE as a data source.

The next version (using an ODBC DSN) works, but I can't seem to get around
making the user type the password again:

wrdDoc.MailMerge.OpenDataSource Name:="<pathtomyqueryfile>.dqy",
LinkToSource:="True", _
Connection:="DSN=<myglobaldsn>;UID=<UID>;PWD=<PWD>;"_
SQL_Statement:="SELECT * FROM <myviewname>"

I was wondering if anyone out there knew the answers to the following
questions:

(1) Is Word 2000 not capable of using the SQLOLEDB provider as a data
source?

(2) Is there anyway to do a mailmerge from an ADP without requiring me to
store the password locally or ask the user for the password again? I cannot
figure out a way to retrieve the user's database password that was typed
when the DB was opened, and I think it's tedious to have to reenter it. This
was trivial with an MDB file, since I would simply specify
"NAME:=CurrentProject.FullName", and it would refer back to my mdb without
requiring another password.

(3) When I use a connection string for ODBC, specifying UID and PWD
parameters explicitly, why is the password ignored? Am I doing something
wrong.

(4) Since I'm explicitly giving a connection string and an SQL statement,
why must I also provide the parameter NAME:="xyz.dqy"' when using ODBC? It
seems superfluous to have to use MSQuary to create a .dqy file for each of
my queries.

I believe I must be overlooking something and there must be a better way to
do what I am trying to do. I'm confused, so any help would be appreciated.

Thanks!

John Kounis

 

[Sylvain Lafontaine]

ADP is not a data source: like Word, it's a program who make access to an
external database but it doesn't contains any table itself.

For Word 2000, you're right: it doesn't offer support for OLEDB; you must
use Word 2002 and even then, you must use an ODC file to specify the
connection parameters:

http://support.microsoft.com/kb/285332/en-us

http://homepage.swissonline.ch/cindymeister/MM2002/MM_VBA02.htm

http://support.microsoft.com/kb/289830/en-us

For the password, I've absolutely no idea why it doesn't work and for the
last question, you should ask on a more appropriate newsgroup than this one.

S. L.

 

[John Kounis]

Thank you very much for the references below. The links helped me solve the
problem.
Basically, the string "DATABASE=<db>" was missing from the DSN. This was
required, even though I had set the default database when I created the DSN.
Specifying the string in this way, also allows me to pass the SQL Server
password as part of the DSN.

However, as far as I can tell, there is no way for me to retrieve the
password that the user typed in when he first started the adp. I can get the
user id, but not the password. Therefore, I have to store a password
somewhere in the database in order to be able to pass it on. The solution I
implemented was to create a "print_only_user", who only has read-only access
to the few views and tables used for mailmerge. I then hardcode the password
for that user in a configuration table.

Thanks again for the help,

John Kounis