Extra Instance of Access When Automating 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;Password="""";User
ID=Admin;Data Source=" & CurrentDb.Name & ";Mode=Read;Extended
Properties="""";Jet

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

 

[Cindy M -WordMVP-]

Replied to duplicate posting in another group.¨

Cindy Meister