Extra Access Instance with Automated Mail Merge

[Neil Ginsberg]

I have a strange situation using Access to automate a Word mail merge. Using
Access 2000 and Word 2000, the code opens Word, opens the document in Word,
sets a table

in the calling Access application as the data source, and then performs a
merge. Everything works fine.

However, when a user uses it in Access 2002 and Word 2002, an extra instance
of the Access application is opened and remains open at the end. Sometimes
it remains open

and the calling application is closed!

The only difference when it's run in 2002 is that the extra parameter:
subtype:=gcon_wdMergeSubTypeWord2000 is given, per MS instructions. (Without
it, the user is

prompted for data source.)

Thinking that this was perhaps a carryover from the problem with the Access
97 but where a custom title bar caused an extra instance of Access to open,
I removed the

custom title bar and left just "Microsoft Access." After doing that,
however, the merge created *two* extra instances of Access when it was run!!

I also tried using ODBC when connecting the data source, instead of the
default DDE. The results were the same.

The user tried running the Access 2000 database as-is in Access 2002, and he
also tried first converting the database to 2002. Results were the same in
both cases.

I tried switching the code to use ODBC instead of DDE, but results were the
same. Tried OLE DB, but didn't have better results there either.

The user is running Windows XP with Access 2002 (10.4302.4219) SP-2; Word
2002 (10.4219.4219) SP-2; and we are using stdole2.tlb (3.50.5014.0).

The code I am running is below. Any thoughts/comments/suggestions would be
appreciated.

Thanks!

Neil

Set objWord = CreateObject("Word.Application")
With objWord
.Documents.Open Filename:="c:\path of document\name of
document.doc", _
ConfirmConversions:=False, _
ReadOnly:=False, _
AddToRecentFiles:=False, _
PasswordDocument:="", _
PasswordTemplate:="", _
Revert:=False, _
WritePasswordDocument:="", _
WritePasswordTemplate:="", _
Format:=gcon_wdOpenFormatAuto 'Constants with
"gcon_wd" represent Word constant values
With .ActiveDocument
If .MailMerge.State = gcon_wdMainAndDataSource Then 'If a mail
merge document
If .MailMerge.DataSource.ConnectString <> "TABLE MyTable" Or
_
.MailMerge.DataSource.Name <> CurrentDb.Name Then ' If
does not have correct data source or data source location...
If MsgBox("This document does not have the correct data
source, or is not using the " & _
"current database as its data source. Would you
like to convert it?", _
vbQuestion + vbYesNo) = vbYes Then
blnRefreshDataSource = True
Else
objWord.Application.Quit gcon_wdDoNotSaveChanges
GoTo Exit_Label
End If
End If

Else
If MsgBox("This document is not a mail merge document. Would
you like to convert it?", _
vbQuestion + vbYesNo) = vbYes Then
blnNotMailMergeDoc = True
blnRefreshDataSource = True
Else
objWord.Application.Quit gcon_wdDoNotSaveChanges
GoTo Exit_Label
End If
End If

If blnNotMailMergeDoc Then
.MailMerge.MainDocumentType = gcon_wdFormLetters
End If

If blnRefreshDataSource Then
If GetWordVersion(objWord) <= gcon_WordVer2000 Then
'Using DDE:
.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
ConfirmConversions:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Connection:="TABLE MyTable", _
SQLStatement:="SELECT * FROM [MyTable]"
Else
'Using DDE:
.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
ConfirmConversions:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Connection:="TABLE MyTable", _
SQLStatement:="SELECT * FROM [MyTable]", _
subtype:=gcon_wdMergeSubTypeWord2000
End If
End If
End With
.Visible = True
.Application.WindowState = 1 'Maximized
.Activate
End With

In the above code, tried using ODBC as follows:

.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
ConfirmConversions:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Connection:="DSN=MS Access Database;DBQ=" & CurrentDb.Name &
_
";DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;", _
SQLStatement:="SELECT * FROM 'MyTable'", _
subtype:=gcon_wdMergeSubTypeWord2000

Then tried OLE DB as follows:

.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
SQLStatement:="SELECT * FROM [MyTable]"

Then recorded a macro establishing data source and used its code, as
follows:

.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:="", _
Revert:=False, _
Format:=gcon_wdOpenFormatAuto, _
Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User
ID=Admin;Data Source=" & CurrentDb.Name & ";Mode=Read;Extended
Properties="""";Jet

OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database
Password="""";Jet OLEDB:Engine Type=", _
SQLStatement:="SELECT * FROM 'MyTable'", SQLStatement1:="", _
subtype:=gcon_wdMergeSubTypeAccess

[Cindy M -WordMVP-]

Hi Neil,

I also tried using ODBC when connecting the data source, instead of the
default DDE. The results were the same.

This is not possible. DDE always runs the application; ODBC never touches
the application, only the data. Might there be something in the mail merge
document that's maintaining a DDE connection (a DATABASE field?)

Are you certain the document is not connected to the database (via DDE)
when it's opened? If it is, it will connect to Access, and changing the
connection later won't "make it not have happened".

Are these documents that were created in Word 2002, or in an earlier
version?

And...

<<However, when a user uses it in Access 2002 and Word 2002, an extra
instance of the Access application is opened and remains open at the end.
Sometimes it remains open and the calling application is closed!>>

Have you very carefully checked your code to make sure you're not opening
and/or closing the database? Perhaps even using something with .Close or
.Exit without specifically saying where it should take place?
(Application.Exit instead of wdApp.Exit, for example)

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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

[Neil Ginsberg]

Hi, Cindy.

"Cindy M -WordMVP-" <C.*********@hispeed.ch> wrote in message
news:VA.0000a4e2.00e7f672@speedy...

Hi Neil,

I also tried using ODBC when connecting the data source, instead of the
default DDE. The results were the same.
This is not possible. DDE always runs the application; ODBC never touches
the application, only the data. Might there be something in the mail merge
document that's maintaining a DDE connection (a DATABASE field?)

I meant that I used ODBC instead of DDE for the data connection, not to run
the application.

In http://support.microsoft.com/?kbid=285332, the article spells out three
data access methods for automated mail merge in Word 2002: OLEDB (the
default), ODBC, and DDE. I was noting that I had tried all three.

Are you certain the document is not connected to the database (via DDE)
when it's opened? If it is, it will connect to Access, and changing the
connection later won't "make it not have happened".
I'm not sure I'm following you here. The document is "connected" to the
database when it is opened (since it uses the database as its data source).
However, the database that's opening the Word doc is also the database that
is used as the data source, so there's no need for a second instance of the
database to be opened.

Indeed, when I run the application in Access 2000, I don't experience the
phenomenon. However, when a user runs it in Access 2002, a second instance
of the database is opened and is visible to the user. Usually this second
instance is closed when the Word document is closed. However, it is common
for the *calling* instance of the database to be closed and the new instance
to remain open when the Word document is closed!

Are these documents that were created in Word 2002, or in an earlier
version?
Word 2002.
And...

<<However, when a user uses it in Access 2002 and Word 2002, an extra
instance of the Access application is opened and remains open at the end.
Sometimes it remains open and the calling application is closed!>>

Have you very carefully checked your code to make sure you're not opening
and/or closing the database?
Yes, positive.
Perhaps even using something with .Close or
.Exit without specifically saying where it should take place?
(Application.Exit instead of wdApp.Exit, for example)
When I close Word, I am using:

objWord.Application.Quit wdDoNotSaveChanges

Do you prefer a different method for closing Word?

Thanks,

Neil


Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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

[Neil Ginsberg]

P.S. I noted in the other message that I use

objWord.Application.Quit wdDoNotSaveChanges

to close the Word app. But that's only in places where the Word app is
closed. In the scenario I described to you (mail merge main document with
database as data source is opened for editing), I do not close the Word app.

Neil
"Cindy M -WordMVP-" <C.*********@hispeed.ch> wrote in message
news:VA.0000a4e2.00e7f672@speedy...

Hi Neil,

I also tried using ODBC when connecting the data source, instead of the
default DDE. The results were the same.

This is not possible. DDE always runs the application; ODBC never touches
the application, only the data. Might there be something in the mail merge
document that's maintaining a DDE connection (a DATABASE field?)

Are you certain the document is not connected to the database (via DDE)
when it's opened? If it is, it will connect to Access, and changing the
connection later won't "make it not have happened".

Are these documents that were created in Word 2002, or in an earlier
version?

And...

<<However, when a user uses it in Access 2002 and Word 2002, an extra
instance of the Access application is opened and remains open at the end.
Sometimes it remains open and the calling application is closed!>>

Have you very carefully checked your code to make sure you're not opening
and/or closing the database? Perhaps even using something with .Close or
.Exit without specifically saying where it should take place?
(Application.Exit instead of wdApp.Exit, for example)

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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

[Cindy M -WordMVP-]

Hi Neil,

I also tried using ODBC when connecting the data source, instead of the
default DDE. The results were the same.

This is not possible. DDE always runs the application; ODBC never touches
the application, only the data. Might there be something in the mail merge
document that's maintaining a DDE connection (a DATABASE field?)

I meant that I used ODBC instead of DDE for the data connection, not to run
the application.

OK, we're not quite on the same wave-length, here. When a main merge document
is opened, that has been connected to a database using DDE, it will start up
the database application. So, IF the main merge document was originally
connected to an Access database using DDE, and your app opens the document,
Access will start up.

Opening a main merge document connected using any other method (ODBC, OLE DB)
will NEVER open the database.

That's why I said you need to check VERY carefully that the documents do not
currently have a DDE connection. With all other applications closed, start up
Word and open one of these documents. Does Access start up? If it does, then
you have a DDE connection in there, and you have to get rid of it.

Since you say your application makes an ODBC connection, try stripping all
connection information out of the document by setting it back to a "Normal
Word document" (first button on the mail merge toolbar in Word 2002/2003).
Save and close. Now test opening it again manually; and with your app.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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

[PC Datasheet]

This should only occur in Office97. Microsoft corrected the problem in
Office2000 and up. Set the application title in Access Tools - Stratup to
"Microsoft Access" and Access should not start.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Cindy M -WordMVP-" <C.*********@hispeed.ch> wrote in message
news:VA.0000a58d.006072db@speedy...

Hi Neil,

> I also tried using ODBC when connecting the data source, instead of the> default DDE. The results were the same.
>
This is not possible. DDE always runs the application; ODBC never touches the application, only the data. Might there be something in the mail merge document that's maintaining a DDE connection (a DATABASE field?)
I meant that I used ODBC instead of DDE for the data connection, not to run the application.

OK, we're not quite on the same wave-length, here. When a main merge

document is opened, that has been connected to a database using DDE, it will start up the database application. So, IF the main merge document was originally
connected to an Access database using DDE, and your app opens the document, Access will start up.

Opening a main merge document connected using any other method (ODBC, OLE DB) will NEVER open the database.

That's why I said you need to check VERY carefully that the documents do not currently have a DDE connection. With all other applications closed, start up Word and open one of these documents. Does Access start up? If it does, then you have a DDE connection in there, and you have to get rid of it.

Since you say your application makes an ODBC connection, try stripping all
connection information out of the document by setting it back to a "Normal
Word document" (first button on the mail merge toolbar in Word 2002/2003).
Save and close. Now test opening it again manually; and with your app.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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

[Neil Ginsberg]

Hi, Cindy.

Sorry about not getting back to you. Didn't see your note until just now
(one of the things I hate about newsgroup messages -- at least when using
Outlook Express as a reader -- you have to go looking for the message).

Anyway, your note is very helpful. And, yes, all of the documents were
originally done using DDE. So your suggestion to strip out the merge data
source info from all of them (except for the fields, themselves) should do
it.

Thanks,

Neil
"Cindy M -WordMVP-" <C.*********@hispeed.ch> wrote in message
news:VA.0000a58d.006072db@speedy...

Hi Neil,

>> I also tried using ODBC when connecting the data source, instead of
>> the
>> default DDE. The results were the same.
>>
> This is not possible. DDE always runs the application; ODBC never
> touches
> the application, only the data. Might there be something in the mail
> merge
> document that's maintaining a DDE connection (a DATABASE field?)

I meant that I used ODBC instead of DDE for the data connection, not to
run
the application.

OK, we're not quite on the same wave-length, here. When a main merge
document
is opened, that has been connected to a database using DDE, it will start
up
the database application. So, IF the main merge document was originally
connected to an Access database using DDE, and your app opens the
document,
Access will start up.

Opening a main merge document connected using any other method (ODBC, OLE
DB)
will NEVER open the database.

That's why I said you need to check VERY carefully that the documents do
not
currently have a DDE connection. With all other applications closed, start
up
Word and open one of these documents. Does Access start up? If it does,
then
you have a DDE connection in there, and you have to get rid of it.

Since you say your application makes an ODBC connection, try stripping all
connection information out of the document by setting it back to a "Normal
Word document" (first button on the mail merge toolbar in Word 2002/2003).
Save and close. Now test opening it again manually; and with your app.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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

[Neil Ginsberg]

One other question. Is there an advantage to using OLE DB over ODBC with an
MDB file as a data source or would ODBC function in about the same way?

Thanks,

Neil

"Cindy M -WordMVP-" <C.*********@hispeed.ch> wrote in message
news:VA.0000a58d.006072db@speedy...

Hi Neil,

>> I also tried using ODBC when connecting the data source, instead of
>> the
>> default DDE. The results were the same.
>>
> This is not possible. DDE always runs the application; ODBC never
> touches
> the application, only the data. Might there be something in the mail
> merge
> document that's maintaining a DDE connection (a DATABASE field?)

I meant that I used ODBC instead of DDE for the data connection, not to
run
the application.

OK, we're not quite on the same wave-length, here. When a main merge
document
is opened, that has been connected to a database using DDE, it will start
up
the database application. So, IF the main merge document was originally
connected to an Access database using DDE, and your app opens the
document,
Access will start up.

Opening a main merge document connected using any other method (ODBC, OLE
DB)
will NEVER open the database.

That's why I said you need to check VERY carefully that the documents do
not
currently have a DDE connection. With all other applications closed, start
up
Word and open one of these documents. Does Access start up? If it does,
then
you have a DDE connection in there, and you have to get rid of it.

Since you say your application makes an ODBC connection, try stripping all
connection information out of the document by setting it back to a "Normal
Word document" (first button on the mail merge toolbar in Word 2002/2003).
Save and close. Now test opening it again manually; and with your app.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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

[Cindy M -WordMVP-]

Hi Neil,

One other question. Is there an advantage to using OLE DB over ODBC with an
MDB file as a data source or would ODBC function in about the same way?

Personally, I perfer ODBC for Access (and Excel). The OLE DB provider uses the
JET ODBC to make the connection, so in essence, you're just adding another
layer. In addition, the JET OLE DB provider has problems on machines
where the OS configured with non-US dates and will switch day/month if both
values could be a valid month (<=12); same problem as developers experience,
BTW.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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

[Neil Ginsberg]

Thanks, that's good to know.

Neil

"Cindy M -WordMVP-" <C.*********@hispeed.ch> wrote in message
news:VA.0000a639.004e6e00@speedy...

Hi Neil,

One other question. Is there an advantage to using OLE DB over ODBC with
an
MDB file as a data source or would ODBC function in about the same way?

Personally, I perfer ODBC for Access (and Excel). The OLE DB provider uses
the
JET ODBC to make the connection, so in essence, you're just adding another
layer. In addition, the JET OLE DB provider has problems on machines
where the OS configured with non-US dates and will switch day/month if
both
values could be a valid month (<=12); same problem as developers
experience,
BTW.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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