Access XP Automation of Word XP Mail Merge - Problem

[Shannon Rotz]

I have an Access XP procedure which automates Word XP, which is used to
create various Word mail merge documents in a database. It works for all of
the documents except one.

In the one that doesn't work, I keep getting Error 5922 "Cannot open the
data source". I've tried manually connecting using "Confirm conversion at
open" at picking "MS Access Databases", then picking ODBC, then the query,
but I get the same message.

Can anyone point me in the right direction to fix this document so that it
will use my code? I've posted it below. Note: the sqlstmt argument is:
"Select * from [{querydef created at run-time}]" (and yes, it is being
created).



Public Function WordMergeDSN(strDocName As String, sqlStmt As String,
AutoMerge As Boolean) As Boolean
On Error GoTo err_WordMergeDSN

Dim wrd As New Word.Application
Dim wrdDoc As Word.Document
Dim strConnection As String
Dim lngMembNo As Long
Dim blTried As Boolean

' OpenDataSource Method Example
wrd.Application.ChangeFileOpenDirectory CurrentDBDir
If strDocName = "New Document" Then
Set wrdDoc = wrd.Documents.Add
Else
Set wrdDoc = wrd.Documents.Add(strDocName)
End If
'Stop
wrd.ActiveWindow.View.ShowHiddenText = True
wrd.Visible = True

strConnection = "DSN=MS Access Database;DBQ=" & CurrentDb.NAME & ";"
With wrdDoc.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource NAME:=CurrentDB.Name, _
LinkToSource:=True, Connection:=strConnection, SQLStatement:=sqlStmt
If AutoMerge = True Then
.Execute True
End If
End With
If AutoMerge = True Then
wrd.Documents("Document1").Close wdDoNotSaveChanges
End If
WordMergeDSN = True
wrd.Activate

exit_WordMergeDSN:
If Not (wrdDoc Is Nothing) Then
Set wrdDoc = Nothing
End If
If Not (wrd Is Nothing) Then
Set wrd = Nothing
End If
Exit Function

err_WordMergeDSN:
Select Case Err.Number
Case Is = 5151
MsgBox ("Cannot find the template '" & strDocName & "'. Please
re-add it to the '" & CurrentDBDir & "' folder and try again.")
wrd.Quit
WordMergeDSN = False
Resume exit_WordMergeDSN
Case Is = 4198
Resume Next
Case 5922
If blTried = False Then
strConnection = "DSN=MS Access Database;DBQ=" &
CurrentDb.NAME & ";"
blTried = True
Resume
Else
MsgBox "Error #" & Err.Number & " - " & Err.Description
Resume exit_WordMergeDSN
End If
Case Else
MsgBox Err.Number & " - " & Err.Description
wrd.Quit
WordMergeDSN = False
Resume exit_WordMergeDSN
End Select

End Function

 

[Cindy M -WordMVP-]

Hi Shannon,

"Cannot open the database" is a catch-all error message that means Word wasn't
able to make a valid connection. Since you're getting this in the UI, we first
need to track down the problem there. You mention you're linking to a query.
Have you ever been able to get mail merge to link to this query, in any
document? What kind of query is it? Is it a "plain old" SELECT query, or does
it contain any aggregate functions? Parameter prompts?

I have an Access XP procedure which automates Word XP, which is used to
create various Word mail merge documents in a database. It works for all of
the documents except one.

In the one that doesn't work, I keep getting Error 5922 "Cannot open the
data source". I've tried manually connecting using "Confirm conversion at
open" at picking "MS Access Databases", then picking ODBC, then the query,
but I get the same message.

Can anyone point me in the right direction to fix this document so that it
will use my code? I've posted it below. Note: the sqlstmt argument is:
"Select * from [{querydef created at run-time}]" (and yes, it is being
created).

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

 

[Shannon Rotz]

Hi Cindy: thanks for replying. To answer your questions:

It is a plain old Select query, with no parameter prompts or aggregate
functions.

More info.: if I try to change the connection to OLEDB inside Word, using
the "Connect to data source" button on the Merge toolbar, I can't see the
query at all. (I can see other queries, but not this one.) I don't know if
this has anything to do with it, but it's suspicious to me ...

Hi Shannon,

"Cannot open the database" is a catch-all error message that means Word
wasn't
able to make a valid connection. Since you're getting this in the UI, we
first
need to track down the problem there. You mention you're linking to a
query.
Have you ever been able to get mail merge to link to this query, in any
document? What kind of query is it? Is it a "plain old" SELECT query, or
does
it contain any aggregate functions? Parameter prompts?

I have an Access XP procedure which automates Word XP, which is used to
create various Word mail merge documents in a database. It works for all
of
the documents except one.

In the one that doesn't work, I keep getting Error 5922 "Cannot open the
data source". I've tried manually connecting using "Confirm conversion
at
open" at picking "MS Access Databases", then picking ODBC, then the
query,
but I get the same message.

Can anyone point me in the right direction to fix this document so that
it
will use my code? I've posted it below. Note: the sqlstmt argument
is:
"Select * from [{querydef created at run-time}]" (and yes, it is being
created).

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


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 Shannon,

It is a plain old Select query, with no parameter prompts or aggregate
functions.

More info.: if I try to change the connection to OLEDB inside Word, using
the "Connect to data source" button on the Merge toolbar, I can't see the
query at all. (I can see other queries, but not this one.) I don't know if
this has anything to do with it, but it's suspicious to me ...

Normally, this would indicate that the type of query is "invalid". We
(word.mailmerge.fields newsgroup regulars) have heard of other problems,
though; haven't been terribly successful in tracking down the causes, however.
As close as we've been able to track it down, sometimes queries won't show up
for OLE DB if there's a problem/question with permissions (who created it, may
others access it, etc.).

Have you successfully used the querydef created at runtime technique when
automating Word, otherwise? I do know there are two ways to create querydefs
using DAO; one of them puts the querydef in a temporary state, the other
actually saves it in the Access database. I'm quite certain the first wouldn't
work for mail merge. And I know the second would cause database bloat, which
might lead you to try the first method?

Why not try an ODBC connection, instead? Unless you're using ANSI-92 wildcards
in the query?

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

 

[Shannon Rotz]

Thanks for the heads up. I was indeed creating the query using a DAO
querydef. I switched to creating a table using the docmd.runSQL statement,
and then started using ODBC, and eventually got it to work.

I must say, though, this has been one of the more frustrating coding
exercises in recent memory. I don't like blindly trying different things in
the hope that, if the coding gods are nice today, it will decide to work ...
is there no reliable way that always works?


Shannon

 

[Cindy M -WordMVP-]

Hi Shannon,

Thanks for the heads up. I was indeed creating the query using a DAO
querydef. I switched to creating a table using the docmd.runSQL statement,
and then started using ODBC, and eventually got it to work.

I must say, though, this has been one of the more frustrating coding
exercises in recent memory. I don't like blindly trying different things in
the hope that, if the coding gods are nice today, it will decide to work ...
is there no reliable way that always works?

Well, mail merge is always a bit of a challenge, just because there are so many
possible permutations (so many possible data sources; variations in the Windows
and Office installations, ect) and Word is very picky about its connections. I
would have thought the queryDef *could* work, if it were being created
permanently, and be in effect before Word tries to link to it. But one does
have to keep in mind that this is a scenario with which an *end-user* will
never be confronted. And Word is primarily designed with the end-user in mind,
not the developer.

This goes especially for mail merge, since the developer could/would supposedly
dump the data directly into a Word document, rather than going with the whole
mail merge overhead.

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