Queries missing from available data sources in mail merge

[faresatpta]

When I try to do a mail merge using a query in one of my Access databases,
not all queries are showing up on the list when I select the database. This
used to work and showed all the queries in that database, but stopped working.

 

[Peter Jamieson]

Which version of Word/Access?

Typically to see all the queries, including parameter queries, queries that
use user-defined Access VBA functions, and so on, you have to connect to
Access using DDE . he default method since Word 2002 has been OLE DB. To
change to DDE, in Word 2002/2003 check Word Tools->Options->General-Confirm
conversions at open, go through the connection process again, and select the
DDE option when you see it. If you're on Word 2007, the option is in Office
Button->Word options->Advanced (near the bottom) and you may also have to
click the "Show all" button in the Confirm Source dialog box.

Other query types can cause problems too, e.g. if they contain wildcards -
but maybe you could let us know if that is the case.

 

[Daniel J. Parietti]

To PEter or ANyone Else Who Might Able to Assist:

I am using Office 2007 and am having the same problem as that related by
the user 'faresatpta'. I followed the directions you provided in your reply
but was not able to find (in the Advanced settings) any option where I can
specify the DDE option for Word documents to communicate with my Access '07
databases.

Any other thoughts. I've been knocking my head on this problem for a few
days and am getting behind in my work. Otherwise, I will have to reinstall
Office '97.

Dan (Let's Go Sail Lightnings!)

 

[Graham Mayor]

Office Button > Word Options > Advanced > General > Check 'Confirm file
format conversion on open'.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Daniel J. Parietti]

THank you Graham. OK. I was able to merge the data into the Word document
and to have the data to appear in the Word document. But now it's taking
forever. and ever and ever... to save the document after I make any cahnges
to. What's going on ?

 

[Peter Jamieson]

Check Office button->Word options->Advanced->General->"Confirm file
format conversion on open" ("General" is near the bottom of the options
on this page)

Then, after you have selected the databaseand clicked Open, you shoud
see a further dialog box titled "Confirm Data Source".

If your data source is in a .mdb, the DDE option should be listed. If it
is a .accdb, you need to check "Show all". Then the completel list of
available options (for all data source types) appreas and you should be
able to locate and select "MS Access databases via DDE (*.mdb, *.mde)"

Although the DDE method appears to function with Access 2007, there are
signs that the old DDE protocol is on its last legs and Access 2007
seems to put more security barriers in the way. NB, if nothing seems to
be opening after you have got through this dialog box, the chances are
that an Access dialog box with either a logon prompt or other security
warning is hiding behind a window on your screen - try Alt-tab (or
whatever) to find it.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Peter Jamieson]

I would guess that it is trying - and failing - to close the data
source. (See my other message about DDE being on its last legs). That
could be because either Word or Access is not seeing the DDE
communicaiton that it is expecting, or because you are not seeing and
responding to a dialog box because it's hiding behind another window, or
because DDE has "hung".

It might or might not help to open Access and your database before you
open the Word document.


Peter Jamieson

http://tips.pjmsn.me.uk

 

[Daniel J. Parietti]

Peter et al.,

THank you for your reply(ies). I have tried all your suggestions and
still have the same problem. BTW, I cannot bring up the "hidden" dialog box
to which you refer. So for now it seems that I must manully reestablish the
data source each and every time I use the mail merge Word document. That is
less user friently than it was when I used Office '97. How can MS allow that
aspect of Office functionality regress in a later version of the product?
That is a sin to the end-user community. I would think that by now MS would
issue some kind of pathc to fix it. AFter all, there must be widw spread use
of mail merge Word doucments with Access databases.

Question: Is there a similar problem with mail merge documents in Office
2003? If not, maybe I should use Office '03 instead of Office '07?

 

[Peter Jamieson]

THank you for your reply(ies). I have tried all your suggestions and
still have the same problem. BTW, I cannot bring up the "hidden" dialog
box
to which you refer.

Just to be sure: are you saying that you never see a "Confirm Data Source"
dialog box? Or are you seeing that, but when you check "Show all," you are
not seeing a further dialog box? If it's the latter, don't expect a further
dialog box - checking the box merely causes the existing list to be
extended.

I have to say that it never fails here, unless perhaps Word is convinced
that the data source is a Word document or some such. In other words, I'd
regard /not/ seeing this dialog box as abnormal behaviour in Word as long as
you are checking the other box in Word Options. So it could be worth going
through some of the general troubleshooting procedures such as doing a
check/repair of your Office installation.

Another possibility is to connect using VBA, e.g. using something like the
following. Once you have connected, saved and closed your document, you
/should/ be able to reopen it without haveing to select the data source. But
probably not if your .mdb is on a network drive. See Graham Mayor's page for
info. on how to deal with macros:

http://www.gmayor.com/installing_macro.htm

Sub ConnectToAccessViaDDE()
' Put the pathname of your Access database here
Const strName = "c:\mydbs\mydb.mdb"
' Put the table or query name here, preceded by
' the word TABLE or QUERY as appropriate
Const strConnection = "QUERY Office Address List"
' Put the query you want here
' (this is the most basic exmaple)
Const strSQL = "SELECT * FROM [Office Address List]"

With ActiveDocument.MailMerge
' Close any existing source
.MainDocumentType = wdNotAMergeDocument
.OpenDataSource _
Name:=strName, _
connection:=strConnection, _
sqlstatement:=strSQL, _
SubType:=wdMergeSubTypeWord2000
End With
End Sub


<<
How can MS allow that

aspect of Office functionality regress in a later version of the product?
That is a sin to the end-user community. I would think that by now MS
would
issue some kind of pathc to fix it. AFter all, there must be widw spread
use
of mail merge Word doucments with Access databases.

Well, I agree with you, but I do not work for Microsoft and nor do any of
the regular contributors here. While Microsoft had good reasons to
"deprecate" the old DDE connection method, in my view it is very unfortunate
that none of the newer methods was ever improved to the point where it could
do (more or less) what DDE could. And the same can be said for other aspects
of Mailmerge. But improvement has clearly not been a commercial priority for
some time.

Question: Is there a similar problem with mail merge documents in Office
2003? If not, maybe I should use Office '03 instead of Office '07?

It has been much the same since Word XP (2002). In your situation, you would
have to weigh up the pros and cons of "being up to date" rather than "being
able to do what you want". As I've said, you /should/ see these connection
options and perhaps some kind of repair to Word would fix that difficulty,
but if there is no other workaround (e.g. exporting the data from Access is
another possibility) that you can get to work, I guess Word 2007 simply
isn't going to do the job for you.

Peter Jamieson