I am running Windows 98 and Word and Access from Office 97.

I have written VB code for a Word macro to automate a mail merge job using
an Access Table as the data source.  Word defaults to using DDE to access
the Access Table but my code causes TWO instances of the Access program to
open, and only one to close when the Main document is closed.

To avoid Access being opened at all Word Help advised using ODBC.  This
caused other problems so I have reverted to using DDE but would like to find
a way of either stopping the SECOND INSTANCE of Access, or if that is not
possible to include code to close it, rather than having to instruct the
user to close it manually.

When stepping through the code the following lines EACH cause an instance of
Access to be opened: -

    ActiveDocument.MailMerge.OpenDataSource Name:=MySource,
LinkToSource:=True,

    ActiveDocument.MailMerge.DataSource.QueryString = _
        "SELECT * FROM MyTable WHERE (Selection criteria) ORDER BY MyField"

When the Main document is closed - without saving changes, in order to sever
the link and preserve its "independence" - it is always the SECOND instance
that is automatically closed.

Peter Scott

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system ( http://www.*-*-*.com/ ).
Version: 6.0.371 / Virus Database: 206 - Release Date: 13/06/02

Hi Peter,

this can be very difficult, or even impossible to do. The *usual* (but not
only) cause is a "custom name" in the Microsoft Access databank windows
Title Bar. The thing you can set via Tools/Startup options (or whatever it's
called, exactly).

If you remove any custom name, you can *usually* keep Access confined to its
single window. But no guarantees. And that's the only way.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
http://www.mvps.org/word
http://go.compuserve.com/MSOfficeForum

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

Hi Cindy,

Many thanks for that tip Cindy.  Removed my custom name so application
window now defaults to *Microsoft Access* and Eureka! my macro only opens
one instance of Access and closes it when the Main document is closed.

I do wish Microsoft would draw attention to these problems or better still
find a *fix* for them.

As I am very new to using newsgroups and I appreciate your advice.  As you
will see this question was sent to two groups simultaneously.  You have
responded from vba.beginners and it and your reply both show up on my folder
for that group.  There is no sign of it at all on the mailmerge.fields group
folder.  Have you any thoughts as to why that should be, or what I may be
doing wrongly?

"Getting invalid results from If...Then...Else".  I made a reply to your


showing.  As you haven't replied I assume it has not reached the group
server.  The icon at the side of your 13/06 message has changed and now
indicates that your message has been replied to.  Again  am very puzzled as
to what has happened.

As two attempts to send it to the group have failed,would you mind if I sent
it direct to you (I have a copy in my Sent Items folder)?  If you agree
please let me know how to identify it so that you won't delete it as unread
"junk".

With many thanks for your help
Peter Scott

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.372 / Virus Database: 207 - Release Date: 20/06/02

Hi Peter,

This particular one will probably never be fixed because MS considers DDE as
totally out-dated and on its way out. Never mind that it's the data
connection method that provides the most flexibility and easiest way to
bring across data formatting...

You can tell 'em you'd like them to pay more attention to it:

Actually, I'm seeing (and replying) from within the mail merge newsgroup.
Haven't been by vba.beginners for a few days, now... (time constraints).

I've never used Outlook Express as my newsreader (I have Virtual Access), so
I don't know all of OE's settings or capabilities. But any newsreader worthy
of the designation is able to "suppress" picking up the duplicates from
"cross-posting" (that's what it's called when you post to more than one
group in one action). And that's the reason cross-posting is preferred over
multiple, single messages: you see everything in one "message thread",
rather than having to chase all over the place to keep track of a
conversation :-)

Actually, I'm seeing both your posts, and my reply to your first one. There
were some problems with the Newsgroup Servers over last weekend and early
this week. Many OE users have had to reset their newsreaders. I don't know
how to do that, but someone in an OE group could tell you. Meanwhile, I'm
copying my reply, here, below my sig.

Note that it's comparatively short and doesn't address all the points in
your original message. because I've given you a new line of approach to look
at (especially after I saw the DDE vs. ODBC question).

Note that anything sent to me via email, even if I don't delete it right
away, may never get answered. I answer questions posted here BEFORE I answer
anything that comes over email. And if I'm busy (which I very much am), I'll
never get around to them. (Except for paying consultant clients, of course)

Cindy Meister
INTER-Solutions, Switzerland
http://www.*-*-*.com/
http://www.*-*-*.com/
http://www.*-*-*.com/

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

Hi Peter,

Well, then you're merging to an Access table. But my observations remain the
same as to what possibilities you have for creating a text data source.
Access-specific question (such as how you do what I suggested) need to be
asked in an Access-specific newsgroup, as I don't have all the details
present in my head to type out.

When I get to this point in your project description, I have to say: "You're
really making it {*filter*} yourself." I don't think the problem here is with
DDE. I think it comes from Word not being very reliable when it comes to
comparing/handling dates.

My recommendation is to stop and take a "think-break". If I were in your
position, I'd be looking at doing this whole thing with VBA, no mail merge.
That will take care of the odd records stuff you've been running into, as
well as the one-to-many relationships.

If you're interested in that, you'll find WdAcc97.zip on my website, as well
as the following article with the download sample useful. (To access this
you need to sign up for a free, one-month trial subscription)

http://www.*-*-*.com/

Cindy Meister
INTER-Solutions, Switzerland
http://www.*-*-*.com/
http://www.*-*-*.com/
http://www.*-*-*.com/

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

Hi Cindy,

OK Point taken, but from your previous replies it seems there are also
*known issues*  with using ODBC which Microsoft needs to attend to.

Thank you so much for having the patience to answer my questions re the
way that newsgroups work and for letting me know it was probably the
problems on their servers last week that made it appear that my messages
had not reached you.

This makes me a bit uneasy.  I described problems I had re date
comparisons which suddenly *disappeared*.  If Word is not very reliable
is it possible that those same or similar problems could re-appear
(assuming I make NO CHANGES to my VB code or Main mailmerge document)
when the macro runs at some future date?

Your suggestion noted.  After much trouble I have at last got my macro
code working (apparently) perfectly.  As the job needs to be run
regularly I will have to use the macro until I can *get my head round*
writing appropriate code to do the whole job without mail merge.

Once again thank you very much for your helpful comments.

Peter Scott

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system ( http://www.*-*-*.com/ ).
Version: 6.0.372 / Virus Database: 207 - Release Date: 21/06/02

Hi Peter,

Well, in its collective head, MS has also moved beyond ODBC... to OLEDB
in Office XP. There are *lots* of problems coming out of the woodwork
with that <sigh>

I'm sorry, I can't remember which date problem that was (too many things
going past my eyes, these days!). As a rule of thumb, I'd say:
- if you tested thoroughly, it should be OK until
    - you update your MDAC (could happen installing newer versions of
Windows or IE, for example)
    - you update to a different version of Word

If we're back to DDE, then you should be OK (for the time-being). ODBC is
more volatile in this respect. The problems aren't with Word, per se, but
with the fact that the interface (ODBC) keeps changing, but not how Word
works with it.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
http://www.mvps.org/word
http://go.compuserve.com/MSOfficeForum

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

Hi Cindy

Many thanks for all your help with my problems and questions.  I have
learned quite a lot in the past couple of weeks.

Regards
Peter Scott

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.372 / Virus Database: 207 - Release Date: 20/06/02