Mail merge with Access DB does not see a query

[SarahB]

I am using Word 2003 and Access 2003

I have a database with many queries, some of which are different types. I
am trying to create a mail merge with a regular Select query.

When I say to use that database as a data source, all of my other select
queries and tables are in the list for me to choose from except that one.

 

[Jeff]

Hi Sarah.

Very strange issue. I just have a couple of questions for you on this. In
access, can you create a "create table" query, and create a table, using the
query you're trying to do the mail merge from? If you do, then are you able
to see that created table?

I'm not sure why it might be doing that. It seems weird. But I'm just
curious if you tried it a different way, if you'd be able to see the
addresses you're looking for.

Jeff

 

[Peter Jamieson]

If you change the connection method to DDE in Word (check Word
Tools|Options|General|COnfirm conversion at open, then go through the
connection process again, and choose DDE when offered, you will probably see
most tables and queries. However, Word has to run a copy of Access to do
that.

By default, Word 2003 uses OLEDB to connect, and certain query types cannot
be used, especially:
a. parameter queries (if you run them in Access you will be prompted for
some values to be plugged into the query)
b. queries that use user-defined functions written in Access VBA

Other types of query, e.g. Union queries, queries using certain functions
(one or two of the financial ones, for example), and queries using wildcards
(*?%?) either may not be visible or may not work.

Peter Jamieson

 

[SarahB]

Hi Jeff,

Thanks, I tried that, it created the table and I could see it.

I looked at some other postings and tried another suggestion. In Word, I
went to the Tools menu and chose Options. On the General tab, I put a check
before the option: Confirm Conversion at Open. Then when I chose my database
as the data source, it pops up a box with some options. I choose the option:
MS Access Databases via DDE. This pops up a box with tabs to choose tables
or queries. Then it let me choose the query.
Sarah

 

[Winston]

I have a related question... Using Access and Word. Mail merge. I
understand that Word Docs store data source in actual doc and therefore the
data source cannot be dynamic... In addition, in a multi user system, I need
the data source in a consistent location (ie DB) so that all users can use
the same mail merge docs... since the data source is a table (and not a
query) then how can I allow multiple users to run a mail merge at the same
time... if they are populating a mail merge table in access, then they will
all populate the same table (in order to use the same documents) and possibly
mix their mail merge data with each other. Is this question clear?

Possible solution: Make the data source a query (not sure how to do this as
the only options I see are tables) and somehow pass a parameter back to
Access from Word so that the query will run properly and select only the mail
merge data for the user running the particular mail merge...

Alternatively, put the mail merge table on the users local pc in a
consistent location so that all users running mail merge see the same data
source (and since it is on their local machine they get only their own
data)... this would work fine except for thin clients.

Is my question clear or confusing.
thanks,
Winston

 

[Peter Jamieson]

Is my question clear or confusing.

Your question is clear. But that does not mean that the answer is clear too

Make the data source a query (not sure how to do this as
the only options I see are tables)

You can use a query as the data source, but certain types of query (e.g.
parameter queries) cannot be used unless you are using DDE to coneect to
Access, and that requires that Word starts Access, and that complicates
everything for the user, except for the fact that in that case, there is a
current user name which can be referenced in a query using the CurrentUser
function, which may mean that it is easier to write a query that does what
you need and invoke it using the correct username.

Alternatively, put the mail merge table on the users local pc in a
consistent location so that all users running mail merge see the same data
source (and since it is on their local machine they get only their own
data)... this would work fine except for thin clients.

This partly depends on what kind of thin client you are using. Even a thin
client user probably has their own folders etc. on a server somewhere and it
might, for example, be possible to arrange that each user has a private
Access database with the same table name that just holds the data they need
for this process.

I think the key questions are
a. how do you identify that a particular record has been created by a
particular user? What query can you write that would return the data entered
by user "x"
b. how does a user's copy of Word find out what the user's name is (or does
it need to)) ?
c. how does Word pass that information to Access/Jet.

I think we need to know how you do (a) before we can make suggestions on (b)
and (c). If you haven't decided how to do (a) yet (because you need to know
how to get the records from Word, maybe you can let us know:
a. what versions of Word/Access you are using
b. how you are managing multi-user security, and whether, for example,
there is a way to getyour users' Access user names from (say) a Windows
environment variable.

Peter Jamieson

 

[Judy Grimes]

Hi!

Maybe I can help. I had the same problem, converting a Mail Merge document
from Word 97 to Word 2003. I could not "see" the query in the list to
reconnect it to the Word document. It turns out the query was a parameter
query and when I removed the parameter, I could "see" it in Word.

So I had a Mail Merge document using a parameter query in my Access database
to select the data for the document. The parameter indicated the record to
be used for the basic data in the document. I can't relink the document with
it's data in Office 2003 without removing the parameter. So I have to
manually find the record to use each time, rather than have the query produce
it. Is there any way to make this work like it did before?

 

[dhall]

Mr. Jamieson,

Thanks for providing this answer! I had the same problem using Access
queries, and your idea worked for me.

 

[Dean]

I have seen the same behavior with some queries not being visible to Word
merges. Use of NZ() in a query or using grouping in the FINAL query seems to
make the query invisible to Word, even though it works fine within Access.

BUT, I've got a bigger problem -- I have a secured database with most users
only allowed to see personnel records within their group. I have a single
user logon for each group and an initial query that restricts records to
those with the group matching currentuser(). This works well WITHIN Access,
but when I try to link (using ODBC) a Word merge to it, I can see the query
name, but I get a Word message telling me that there are no records.