Re: Word 2000/2002 - Proper Mail Merge steps for ODBC?

From: Tony_VBACoder (anonymous_at_discussions.microsoft.com)
Date: 09/01/04 

Date: Wed, 1 Sep 2004 13:49:59 -0700

Peter, thanks for all your help.

I was able to find the MS Query button and locate my
correct Query, and everything is working great.

There are still a couple of other issues that I am running
into in terms of wanting to pull data from a Parameter
Query in Access, and I am also running into the "The
Database has been placed in a state by user 'bob' on
machine 'mach123' that prevents it from being opened or
locked" error. But I will be posting these as separate
issues as soon as I do a little more testing so that I can
properly post what is going on.

Thanks again.

>-----Original Message-----
>> 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?
>
>WHat I mean is that the OLEDB connection is simply not
available in Word
>2000. So the only option (other than DDE) that will work
in both is ODBC.
>However, that does not mean that Word 2002 will correctly
recognise an ODBC
>data source which has been set up in Word 2000 and vice
versa. So...
>
>> 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).
>
>....you /may/ need to create Word 2000 and Word 2002
versions of each mail
>merge app. I can't remember off the top of my head so you
will need to
>check. I would also take a bit of time to ensure that
your users can do what
>they need - e.g. if they need to apply their own
filters/sorting, you may
>need to confirm that this approach allows them to do so.
>
>> 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?
>
>Assuming you meant "certain that I am using the ODBC
method", then you
>either need to go via MS Query, or go the VBA
OpenDataSource route, or check
>Word Tools|Options|General|"Confirm conversions at open",
go through the
>Select Data Source dialog, pick your .mdb, then select
the User/System DSN
>from the list of possible connection options. If you
don't see it there, you
>will need to use MS Query or VBA.
>
>--
>Peter Jamieson
>
>"Tony_VBACoder" <anonymous@discussions.microsoft.com>
wrote in message
>news:010001c4904a$5f303130$a401280a@phx.gbl...
>> 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" <anonymous@discussions.microsoft.com>
>> wrote in message
>> >news:42db01c49037$6c339f60$a601280a@phx.gbl...
>> >> 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 Tools>Letters and
>> >> Mailings>Mail 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?
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>
>.
>

Quantcast