Peter, thank you for all your insight...it will take me a
little while to absorb it and test it out with both Word
2000 and 2002. I do have one question in regards to
something you mentioned:
First, if you want to connect the same way in Word 2000
and Word 2002, you have to use ODBC (or the old DDE
method).....But in addition, you may find that ODBC
connections set up in Word 2000 are discarded when you
open the .doc in WOrd 2002 and/or vice versa.
Here, you first said that I should go the ODBC route if I
want to get this to work in both Word 2000 and Word 2002,
but then you go onto say that an "ODBC connection set up
in Word 2000 are discarded when you open the .doc in Word
2002". I am a little confused here. Will this method
(ODBC) work in both Word 2000 and Word 2002? I will be
needing this work with both versions, where I will
probably end of creating the final Word Mail Merge .doc
using Word 2000 because about 90% of the users have Office
2000, whereas the rest of Office XP (Word 2002).
In regards to using ODBC, you mentioned "Unfortunately,
the route you have taken in your example actually uses
OLEDB, using the ODBC provider for OLEDB". If I wanted to
use the ODBC method, what should I have done so that I can
be certain that I am using the OLEDB method?
Thanks
-----Original Message-----
Question: Is this the proper way of getting a Word
Document to connect to a Secured Access Database, by
having to create a separate Connection File for each
Mail
Merge Letter I am wanting to create?
IMO there is no "proper" way. At best, there may be a way
that works for
your application and minimises problems if you need to
distribute your
solution to mutliple systems.
There are a number of issues here and unfortunately I
don't think I can give
you reliable suggestions in all cases, but here goes.
First, if you want to connect the same way in Word 2000
and Word 2002, you
have to use ODBC (or the old DDE method). OLEDB is only
supported in Word
2002 and later. Unfortunately, the route you have taken
in your example
actually uses OLEDB, using the ODBC provider for OLEDB.
Any connection that
uses a .odc (or .udl, as we will see) is actually using
OLEDB. So those
connections won't work with Word 2000. But in addition,
you may find that
ODBC connections set up in Word 2000 are discarded when
you open the .doc in
WOrd 2002 and/or vice versa.
This brings up the "Select Data Source" dialog box.
Here,
the "My Data Sources" is selected in the "Look in:"
combo
box and all my ODBC entries are showing EXCEPT my newly
created Access ODBC entry I created in my above steps
(a-
The reason (probably) that some ODBC entries appear here
and some do not is
that there are three types of ODBC DSN, as follows:
FIle DSNs: these are just text files in "INI" format that
contain the
connection information you provided in the ODBC
administrator. Typically
they have a .dsn extension. These are probably the ones
you can see
Machine DSNs: these DSNs are not stored in text files.
They are stored in
the Windows registry, so I do not think you will see them
listed in "My Data
Sources". There are two types of Machine DSN:
a. User DSNs. These are intended to be visible to a
single user on a
machine which may have several different users. They are
stored in the
per-user part of the registry.
b. System DSNs (the type you created). These are
intended to be visible to
all users on a particular machine.
Neither Word 2000 nor Word 2002, as far as I know, lets
you open a .dsn file
directly (in the Open/Select Data Source dialog box).
Word just tries to get
the data directly from the .dsn file, not from the
database it refers to. If
you are trying to open a file data source such as a .mdb
in Word 2000, you
can select the .mdb in the Open Data Source dialog,
check "Select method",
and click Open. If the DSN is a Machine DSN you will
probably see it in the
list of connection methods. Otherwise, you have to use MS
Query to select
the DSN (of any type) and set up the connection. You can
do the same in Word
2002, although MS Query is a little harder to find - it's
on the Tools menu
in the top right hand corner of the Select Data Source
dialog. (You may also
find you need to install MS Query to get what you want).
In both Word 2000 and 2002, you should only need one DSN
to allow you to use
all the tables and queries in your database that are
accessible via ODBC.
But you will need to go through the MS Query route to
choose the table/query
or define your own.
An alternative to all this is to write, or macro record,
a short macro
containing an OpenDataSource command that uses an ODBC
connection string in
the Connection parameter. Then you have to work out the
corrct SQL to get
the data you want, and insert it in the SQLStatement
parameter, then run the
macro. You will find examples I've posted if you search
this newsgroup, e.g.
using Google. One of the problems here is that Word 2002
requires an
additional parameter (Subtype) if you want to connect via
ODBC.
If you are /only/ concerned with Word 2002 and prefer to
use OLEDB, there
are a couple of ways you can proceed without creating an
ODBC DSN.
a. use a .udl file. If you create a Notepad (.txt) file
and rename it to
something.udl, then double-click on the file name, you
should see the
Datalink editor. This is like, if not identical to, the
editor you used to
select your DSN in your point 2. However,
a. in the Provider tab, select the Jet provider rather
than the ODBC
provider
b. in the Connection tab, you may need to check Blank
password and Allow
saving password
c. in the All tab, you will need to edit the valueJet
OLEDB:System databse
to be the pathname of your workgroup file.
d. When you click OK, you do not see the questions that
come at the end of
the dialog you used to set up your .odc
If you select this file as your data source, you should
be presented with a
list of tables, so you only really need one .udl for all
your documents. It
may be possible to use it to connect to a specific table
or query, but
that's for you to discover if you want.
Alternatively, you can set up a .odc using much the same
process you used
before in your point (2), but choose the Other/Advanced
option instead of
ODBC DSN. Then fill in the datalink dialog options in the
same way as for
the .udl file above. When you get to save the .odc, there
is a checkbox that
lets you specify whether your .odc should cause Word to
display a list of
tables/queries within your .mdb, or whether Word should
connect to a
prespecified table/query. Again, if you use the former,
you should be able
to use a single .odc for all your mailmerge documents.
--
Peter Jamieson
"Tony_VBACoder"
wrote in message
...
I am new at setting up a Word document as a Mail Merge
to
an Access Database. My situation involves a secured
Access 2000/2002 Database, with a Word 2000/2002 Mail
Merge Letter. My Access Databases are secured with a
MDW
Security File required to open my Database. I have
various queries within my Access database are specific
for
each Word Mail Merge document. In my Access DB
workgroup
security file, I have created a user called "WordUser"
whose sole purpose is to be able to run the Word Mail
Merge Letters. This user has no password and only
has "Read" permission on all the tables/queries required
for each letter and nothing else. I have had no luck
being able to get my Word 2002 letter to connect to my
secured Access database with the Mail Merge Wizard
within
Word (I have posted a few questions in the newsgroups,
but
have not gotten any responses). So instead, I have
tried
to go the ODBC route, where I would create a new DSN
entry
that points to my Access database, in hopes that I could
use this ODBC entry as my datasource for my Word Mail
Merge document. However, this method has also raised
some
questions that I hope someone can answer for me or help
in
the right direction.
1) To create a new ODBC entry, I do the following:
a) click on "System DSN" Tab in the ODBC Data Source
Administrator
b) click the "Add" button
c) select "Microsoft Access Driver (*.mdb)"
d) click the "Finish" button
e) In the "ODBC Microsoft Access Setup" Form I:
f) enter "MyDB" in the "Data Source Name:" text box
g) click the "Select..." button in the "Database"
Frame
to located my Access Database
h) click the "Advanced..." button
i) enter my "WordUser" in the "Login name:" text box
in
the "Set Advanced Options" form and click the "OK"
button.
j) select the "Database:" radio button in the "System
Database" frame
k) click the "System Database..." button to select my
MDW file for my secured database
l) click the "OK" button on the ODBC Microsoft Access
Setup form to complete setting up my new ODBC entry
2) Now, once my ODBC entry has been created, I go to my
Word 2002/2000 letter and select ToolsLetters and
MailingsMail Merge Wizard. At the Wizard's Step #3, I
select "Browse" from the "Use an existing list" option.
This brings up the "Select Data Source" dialog box.
Here,
the "My Data Sources" is selected in the "Look in:"
combo
box and all my ODBC entries are showing EXCEPT my newly
created Access ODBC entry I created in my above steps
(a-
l). However, there are 2 entries that I am not familiar
with: "+Connect to New Data Source.odc" and "+New SQL
Server Connection.odc". I selected the "+Connect to New
Data Source.odc" option and it took me into a new "Data
Connection Wizard" where I did the following:
a) Select "ODBC DSN" from the "What kind of data
source
do you want to connect to?" list and clicked the "Next"
button
b) Now my newly created ODBC entry shows up in
the "ODBC data sources" list. I selected it and clicked
the "Next" button
c) At this point, all my tables and queries were
listed. From the list, I selected my query for the
letter
I am creating and clicked the "Next" button.
d) Now, the "Save Data Connection File and Finish"
dialog box is shown, prompting me to save this as a
connection file (*.odc). I entered a name and clicked
the "Finish" button.
Question: Is this the proper way of getting a Word
Document to connect to a Secured Access Database, by
having to create a separate Connection File for each
Mail
Merge Letter I am wanting to create?
.
|