Problems with automated Mail Merge from Access 2000 and XP

[mljames]

I'm working on a project where I am trying to merge documents i
response to a button click on an Access form.

I'm copied some code fragments below to show you what I have in plac
right now.


Code
-------------------

Public Function InitializeWord() As Boolean
On Error Resume Next
Dim objTemp As Word.Application
Set gobjWordApp = GetObject(, "Word.Application")
If Err <> 0 Then 'Word was not running, so we'll want to close it when we're done.
Err.Clear
Set objTemp = CreateObject("Word.Application") 'necessary workaround for automation bug see mskb Q188546
Set gobjWordApp = CreateObject("Word.Application")
objTemp.Quit
Set objTemp = Nothing
gfWordWasRunning = False
Else
gfWordWasRunning = True
End If
InitializeWord = True
End Function

-------------------

Code
-------------------

Public Function QueueDocs(frm As Form) As Boolean
Dim objWordDoc As cWordDoc
Dim varDoc
Dim rstDoc As DAO.Recordset
Dim strMsg As String
Set rstDoc = frm.RecordsetClone
With rstDoc
.MoveFirst
Set mcolWordDoc = New Collection
Do Until .EOF
If !SelectedYN Then
If ValidateQuery(Nz(!DocQuery, "None"), !DocName, !DocCriteriaName) Then
' ValidateQuery checks to make sure that data exists before allowing the merge to continue.
Set objWordDoc = New cWordDoc
objWordDoc.DocName = !DocName
objWordDoc.DocPath = gstrRootDir & !DocPath
objWordDoc.DocQuery = Nz(!DocQuery, "None")
objWordDoc.DocFunction = Nz(!DocFunction, "")
objWordDoc.DocCriteria = Nz(!DocCriteriaName, "")
mcolWordDoc.Add objWordDoc
End If
End If
.MoveNext
Loop
End With
rstDoc.Close
QueueDocs = True
End Function

-------------------

Code
-------------------

Public Function SendToMerge(mcolWordDoc, bEdit As Boolean) As Boolean
'loop through collection sending one doc at a time to printer
On Error GoTo SendToMergeErr
Dim varDoc
Dim strMsg As String
Dim varStatus
Dim strSQLMerge As String
Dim strSource As String
Dim n As Long

For Each varDoc In mcolWordDoc
If bEdit Then
strMsg = "Previewing " & CStr(varDoc.DocName)
Else
strMsg = "Printing " & CStr(varDoc.DocName)
End If
varStatus = SysCmd(acSysCmdSetStatus, strMsg)
strSource = "DSN=" & gstrcDSNname
Select Case varDoc.DocCriteria
' sets up the strSQLMerge string. i can't include it here for proprietary reasons.
End Select

'Open existing Word Document
With gobjWordApp
.Documents.Open filename:=varDoc.DocPath

Dim templateDoc As Word.Document
Set templateDoc = .ActiveDocument

If Len(varDoc.DocFunction) <> 0 Then
Run varDoc.DocFunction, gobjWordApp, Nz(Screen.ActiveForm!cbo1.Column(1), ""), _
Nz(Screen.ActiveForm!txtID1, "-1"), Nz(Screen.ActiveForm!txtID2, "-1")
End If

With .ActiveDocument.MailMerge
.OpenDataSource Name:=CurrentDb.Name, ConfirmConversions:=False, _
ReadOnly:=True, linkToSource:=True, Connection:=strSource, _
SQLStatement:=strSQLMerge ', SubType:=wdMergeSubTypeWord2000
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute
End With

If Not bEdit Then
'following line needed per kb article #Q159328
.Application.Options.PrintBackground = False
.Application.ActiveDocument.PrintOut
.ActiveDocument.Close wdDoNotSaveChanges
.ActiveDocument.Close wdDoNotSaveChanges 'required twice because of merging to new doc.
Else
templateDoc.Close wdDoNotSaveChanges
' .Visible = True
End If
End With
IterateCollection:
DoEvents 'needed to allow the process to be interrupted
Next varDoc
SendToMerge = True
SendToMergeExit:
Exit Function
SendToMergeErr:
Select Case Err
Case 5922
strMsg = "Word was unable to open the data source for " & varDoc.DocName & ".@@"
strMsg = strMsg & "This document will be skipped for now..."
MsgBox strMsg, vbOKOnly + vbCritical, "Uh-oh"
Err.Clear
Resume IterateCollection
Case 5174 'word doc not found
strMsg = "Word was unable to locate " & vbCrLf & varDoc.DocName & ".@@"
strMsg = strMsg & "Please verify the name and location of the document."
MsgBox strMsg, vbOKOnly + vbCritical
Resume IterateCollection
Case Else
If Err <> 0 Then
MsgBox Err & ":" & Err.Description
End If
Resume SendToMergeExit
End Select
End Function

--------------------

Code:
--------------------

Public Function TerminateWord() As Boolean
gobjWordApp.Application.Quit wdDoNotSaveChanges
Set gobjWordApp = Nothing
TerminateWord = True
End Function

--------------------

Code:
--------------------

Public Function rgbProcessMailMerge(frm As Form, bPreview As Boolean)
Dim fContinue As Boolean
Dim strMsg As String
Dim varReturn

'Create DSN once
strMsg = "Registering DSN..."
varReturn = SysCmd(acSysCmdSetStatus, strMsg)
fContinue = CreateDSN ' This creates a DSN for the current database using the gstrcDSNname variable
If fContinue Then
'Initialize Word
strMsg = "Locating Microsoft Word..."
varReturn = SysCmd(acSysCmdSetStatus, strMsg)
fContinue = InitializeWord
If fContinue Then
'Queue docs
strMsg = "Queueing documents..."
varReturn = SysCmd(acSysCmdSetStatus, strMsg)
fContinue = QueueDocs(frm)
If fContinue Then
'Send to merge
strMsg = "Sending documents to printer..."
varReturn = SysCmd(acSysCmdSetStatus, strMsg)
fContinue = SendToMerge(mcolWordDoc, bPreview)
If Not fContinue Then
If bPreview Then
strMsg = "There was a problem sending documents to Word."
Else
strMsg = "There was a problem sending documents to printer."
End If
MsgBox strMsg, vbOKOnly + vbExclamation
End If
Else
strMsg = "There was a problem queueing the documents."
MsgBox strMsg, vbOKOnly + vbExclamation
End If

If Not bPreview Then
'Terminate Word
strMsg = "Wrapping things up..."
varReturn = SysCmd(acSysCmdSetStatus, strMsg)
fContinue = TerminateWord
If Not fContinue Then
strMsg = "There was a problem finishing the process."
MsgBox strMsg, vbOKOnly + vbExclamation
End If
Else
gobjWordApp.Visible = True
End If
Else
strMsg = "There was a problem locating Microsoft Word."
MsgBox strMsg, vbOKOnly + vbExclamation
End If
Else
strMsg = "There was a problem registering the data source."
MsgBox strMsg, vbOKOnly + vbExclamation
End If

'don't forget to clear status!
varReturn = SysCmd(acSysCmdClearStatus)

End Function

--------------------


Some additional information:
- The rgbProcessMailMerge function is the start of the whole thing.
- The varDoc variable is used to facilitate a loop and print multiple
documents off of a document list at a single button click.
- The Run command used right before the mail merge is used to fill in
data that can't be done with a simple query. Namely, these are loops
to fill names of people into the document when the person count is
unknown at design time. The parameters passed in are key data that
distinguish this merge from another. Data is filled in through the use
of bookmarks in the "template" document.
- This needs to work in both Access 2000 and XP, with the corresponding
Word applications. At compile time, the database, which starts out in
an Access 2000 format, is converted to a second database in an Access
XP format. Then, each of the databases is compiled into an mde file.
Before compiling, I also uncomment the :SubType parameter of the
.OpenDataSource method.

- Currently, the Access 2000 version tends to be pretty stable. There
are the occasional errors, but overall, it does well.
- The Access XP version, however, is not performing well at all. It
frequently crashes during the merge process. Also, once I get an error
during the merge, I then get "remote server unavailable" messages until
I restart Access.
- Both versions also, occasionally, give me "Unable to find datasource
errors". I thought for a while that the presence of header information
in the base document was causing this, but I (tried to) remove all of
those, and the errors continue.


On to my questions:
- Would it help if I didn't convert the database to Access XP, but
instead just compiled the mde file in XP with a 2000 formatted
database? Is that even possible?
- Does the :SubType parameter do what it is supposed to do according to
the knowledge base articles that I've read? Is it really necessary?
- Would using the .MainDocumentType property of the MailMerge object
help at all? I tried it once with wdNotAMergeDocument to see if it
still worked and it did. Would this help avoid the data source
errors?
- Does anyone have any thoughts or ideas on how I could stabilize this
process for both versions of Access?

Thanks,
Matt James

 

[Cindy M -WordMVP-]

Hi Mljames,

I simply don't have time to read through all the code you posted (which
is why you haven't gotten any answers before now - it's just looks too
time-consuming for everyone, I suspect).

- Currently, the Access 2000 version tends to be pretty stable. There
are the occasional errors, but overall, it does well.
- The Access XP version, however, is not performing well at all. It
frequently crashes during the merge process. Also, once I get an error
during the merge, I then get "remote server unavailable" messages until
I restart Access.

This sounds like an orphaned "pointer" to the Word application that's
leaving a "stub". Check in the Task Manager when this happens and you'll
probably find winword.exe still listed. you'd need to track down what's
causing the error, and try to trap it so that you can close correctly.

- Both versions also, occasionally, give me "Unable to find datasource
errors". I thought for a while that the presence of header information
in the base document was causing this, but I (tried to) remove all of
those, and the errors continue.

Is there any user interaction at all with this? Or are any filters being
set and saved with the file? Are you opening files that are already
attached to an (Access?) data source, or are you attaching the data
source dynamically each time?

Is this only happening on particular machines? And then regularly on
these machines?

On to my questions:
- Would it help if I didn't convert the database to Access XP, but
instead just compiled the mde file in XP with a 2000 formatted
database? Is that even possible?

Haven't the foggiest; you'd need to ask this in an Access group. The main
difference you have to watch out for, as far as mail merge is concerned,
is that the OLE DB connection method uses ANSI-92 standard. This wasn't
supported in earlier versions of Word or Access. And in Access 2002/2003
you can choose which to use for a database. The critical point is the
wildcards that are recognized for queries.

- Does the :SubType parameter do what it is supposed to do according to
the knowledge base articles that I've read? Is it really necessary?

Yes, and yes!

- Would using the .MainDocumentType property of the MailMerge object
help at all? I tried it once with wdNotAMergeDocument to see if it
still worked and it did. Would this help avoid the data source
errors?

If you set it to wdNotAMergeDocument you are unlinking the data source.
You may certainly do this when closing a main merge document, and should
if you're linking in the data source dynamically (OpenDataSource method)
each time.

- Does anyone have any thoughts or ideas on how I could stabilize this
process for both versions of Access?

Without understanding more about how things are set up and expected to
run, and what's causing the "crashes", it's impossible to say.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
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

 

[mljames]

OK. An update and more questions.

- The remote server problems seem to have gone away. I found a
article telling me to qualify my function calls, which I wasn't doin
when I was using InchesToPoints() in my alteration functions.
- Most of the other errors that I'm seeing do go away if I add th
.MainDocumentType = wdNotAMergeDocument statement in. However, I stil
end up with "#5631 Word could not merge main doc with data sourc
because data records were empty or no data records matched quer
options" on the occasional document.
- Now, if I remove the :SubType=wdMergeSubTypeWord2000 parameter
everything gets happy! All of the functionality still works, which i
contrary to almost everything that I've read. I did find one referenc
(http://homepage.swissonline.ch/cindymeister/MM2002/MM_VBA02.htm) tha
makes it sound like if I'm connecting through an ODBC/DSN (which I am)
then the extra parameter is unnecessary. (Looking at the addres
again, this looks like your (the previous replier's) web page. Am
reading the "ODBC connections should continue to work as previously.
statement wrong?)

Does anyone have any ideas on this? I apologize for the original pos
with all of it's code, but I wanted to give people the opportunity t
look at what I'm doing and try to see the "dumb" mistake I was making.

I'd appreciate any help anyone could give me on this issue.

Mat

 

[Peter Jamieson]

makes it sound like if I'm connecting through an ODBC/DSN (which I am),

then the extra parameter is unnecessary.

My guess, based on experiments here, is that although you are providing an
ODBC DSN, the connection is actually being made using OLEDB. And that may
work fine in your situation as long as e.g. you are always able to connect
to all the queries you need, and are not getting data formatted n unexpected
ways.

You can do two things to verify this. The first is to display the value of

ActiveDocument.MailMerge.DataSource.Type

after the connection has been made. It will probably be 5 (wdMergeInforFrom
ODSO) rather than 4 (wdMergeInfoFromODBC). Not proof, but...

The second is to discover the connection string Word is actually using, i.e.

ActiveDocument.MailMerge.DataSource.ConnectString

Unfortunately, attempts to display that value fail in Word 2002 (should be
fixed in Word 2003). But you can do it indirectly by saving your document in
HTML format and re-opening it as a plain text file - the merge information
is laid out for you near the top of the file. I predict you will see an
OLEDB Provider name etc. and no sign of your DSN.

So what is happening?

Well, I don't know exactly how Word decides how to connect, but in most
cases it seems to look at the Name parameter /before/ the Connection
parameter when deciding how to connect.
a. If you specify a .mdb in the Name and no Subtype parameter, Word 2002
will try to use OLEDB first.
b. if you specify a .mdb in the Name and Subtype=wdMergeSubtypeWord2000,
Word 2002 will try to open using DDE, in which case it will look for a TABLE
table/queryname type connection string. If it finds an ODBC connection
string instead it will typically pop up a select file box rather than a
select query name box.
c. the only way to /guarantee/ an ODBC connection is to leave the Name
blank. But in Word 2002, that rquires you to use wdMergeSubtypeWord2000.

So again, all's well if OLEDB is OK for you.

There is more in case (a). If for example the database is secured, the OLEDB
connection will fail and Word will pop up the Datalink dialog. But I think
it ignores anything you put in there. It then seems to attempt to connect
using ODBC, and if you happen to have provided a complete ODBC connection
string with the necessary security info, it will connect using ODBC. But in
that case, to avoid the dialog in an automation scenario, you must either
a. deliberately open using ODBC (Name="", use Subtype) or
b. deliberately open using a .odc file (rather than the name of the .mdb)
that specifies the security information

 

[Cindy M -WordMVP-]

Hi Mljames,

Does anyone have any ideas on this? I apologize for the original post
with all of it's code, but I wanted to give people the opportunity to
look at what I'm doing

That's OK. Better safe than sorry ; nothing to apologize for. So much
code, before even seeing the problem statement was just so...
intimidating. I wanted to let you know why you'd had to wait, and why the
answer might not be complete!

The best thing to do at this point would be to test in the UI what works,
and what does not. The information on my website is UI-specific and does
not refer to the VBA interface. At least, not directly.

Note that there are various wdMergeSubType constants, and that
SubTypeWord2000 would usually mean to use DDE. There is another one
(can't say which, off the top of my head) that Word uses for ODBC
connections. I'd say: record a macro connecting to the data and see what
syntax that uses.

- Most of the other errors that I'm seeing do go away if I add the
.MainDocumentType = wdNotAMergeDocument statement in. However, I still
end up with "#5631 Word could not merge main doc with data source
because data records were empty or no data records matched query
options" on the occasional document.
- Now, if I remove the :SubType=wdMergeSubTypeWord2000 parameter,
everything gets happy! All of the functionality still works, which is
contrary to almost everything that I've read. I did find one reference
(http://homepage.swissonline.ch/cindymeister/MM2002/MM_VBA02.htm) that
makes it sound like if I'm connecting through an ODBC/DSN (which I am),
then the extra parameter is unnecessary. (Looking at the address
again, this looks like your (the previous replier's) web page. Am I
reading the "ODBC connections should continue to work as previously."
statement wrong?)

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
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

 

[mljames]

I've talked to one of the original developers on the project that I'
seeing this problem on. She seemed to indicate that they added th
:SubType=wdMergeSubTypeWord2000 only because there were documents tha
were last saved in Word 2000, but trying to be merged in AccessXP.
Documents last saved in Word XP were OK without the parameter.

Does this sound correct to anyone

 

[Peter Jamieson]

Could well be. If the Word 2000 doc is opened in Word 2002 it may not merge
unless they execute a new OpenDataSource. At that point the probably went
for adding the parameter rather than trying to rejig the call so that it
specifically used OLEDB.

 

[icecrew]

Hiya

In "Public Function SendToMerge" you used the With..End With block
That's what's causing the "server unavailable" problem. To get aroun
it you have to remove the "With" block & use full references to th
Word objects.

Instead of ...
With gobjWordApp
.Documents.Open filename:=varDoc.DocPath

Dim templateDoc As Word.Document
Set templateDoc = .ActiveDocument

Use ...
gobjWordApp.Documents.Open filename:=varDoc.DocPath
Dim Dim templateDoc As Word.Document
Set templateDoc = gobjWordApp.ActiveDocument

It's more cumbersome coding but is the only known workaround. It'
documented somewhere in the MS Knowledge Base.

Hope this helps :cool