Using Access as mail merge data source

[Jacob Behounek]

I am creating a mail merge using Access (Word 2003 and Access 2003) as the
data source. When I try to locate the query I have created, I do not see it
listed in the mail merge dialog box (Select Table). The name of the query
isn't suspect, there are no parameter prompts that might confuse Word. I'm
baffled. Please give me some guidance or a potential reference as to the
specifications a query must meet before being accepted by Word.

 

[Doug Robbins - Word MVP]

In Word, from the Tools menu, select Options and then go to the General tab
and check the box for "Confirm conversions at open" Then when you select
the data source, you will be asked for the method by which the connection
should be made. Try the different options.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Jacob Behounek]

Excellent suggestion and thanks! I tried it and it seemed to do exactly what
I needed. Unfortunately, I had already gone the longer (and probably better)
route of figuring out why and altering the situation.

The why of the matter is that Word only allows certain types of queries and
tables to be displayed when doing a mail merge (the "Confirm conversions at
open" method notwithstanding). The types it allows are TABLES and VIEWS.
These definitions should cover most of what you'll see in Access with the
exception of LINKS (tables in Access linked from another database) and PROCS
(Stored procedures, another type of query). While there is no way in Access
to determine which type of query you're dealing with (short of doing what I
did and write the ADOX code to tell the difference), the effect if apparent:
you can't see PROCS in Word's mail merge. The cause of problem was that I
was using a User-Defined-Function in my query that was changing it from being
seen as a VIEW to a PROC. Long description, short solution: if at all
possible avoid using UDF's in queries you intend to merge. If you must use
UDF's, use Doug's solution as it allows you to see all of the queries you've
built.

Again, thanks for your help!!
Jacob