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

From: Peter Jamieson (pjj_at_KillmapSpjjnet.demon.co.uk)
Date: 09/02/04

  • Next message: Peter Jamieson: "Re: Database toolbar" 
    Date: Thu, 2 Sep 2004 09:32:28 +0100

    FWIW,
     a. for parameter queries, you have to use DDE because it is Access that
    displays the pop-ups that solicit the parameter values from the user, and
    DDE is the only connection method that actually starts Access.
     b. although you can /probably/ specify the access mode Word will use when
    using a .udl, .odc or ODBC Connection parameter in VBA (there is a tab for
    this in the Datalink editor),
        - it obviously won't help if someone else is opening the database in an
    incompatible sharing mode (e.g. some sort of exclsive access mode)
        - I get the impression that by default, when Word connects to a .mdb
    using OLEDB, it uses a more exclusive mode than it really should. I'm not
    completely convinced that anything you specify in a .odc will change that.
    But I don't know the facts on that one. 

    -- 
Peter Jamieson
"Tony_VBACoder" <anonymous@discussions.microsoft.com> wrote in message
news:45bf01c49065$3edfd690$a501280a@phx.gbl...
> 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?
> >> >>
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
  • Next message: Peter Jamieson: "Re: Database toolbar"
    Loading