Reply
 
Thread Tools Display Modes
  #1   Report Post  
SarahB
 
Posts: n/a
Default Mail merge with Access DB does not see a query

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.
  #2   Report Post  
Jeff
 
Posts: n/a
Default

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

"SarahB" wrote:

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.

  #3   Report Post  
Peter Jamieson
 
Posts: n/a
Default

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" wrote in message
...
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.



  #4   Report Post  
SarahB
 
Posts: n/a
Default

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

"Jeff" wrote:

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

"SarahB" wrote:

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.

  #5   Report Post  
Winston
 
Posts: n/a
Default

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" wrote:

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" wrote in message
...
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.






  #6   Report Post  
Peter Jamieson
 
Posts: n/a
Default

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


"Winston" wrote in message
news
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" wrote:

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" wrote in message
...
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.






  #7   Report Post  
Judy Grimes
 
Posts: n/a
Default

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?

"Jeff" wrote:

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

"SarahB" wrote:

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.

  #8   Report Post  
Posted to microsoft.public.word.mailmerge.fields
dhall
 
Posts: n/a
Default Mail merge with Access DB does not see a query

Mr. Jamieson,

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


"Peter Jamieson" wrote:

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" wrote in message
...
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.




  #9   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Dean
 
Posts: n/a
Default Mail merge with Access DB does not see a query

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.

"Judy Grimes" wrote:

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?

"Jeff" wrote:

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

"SarahB" wrote:

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.

Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
mail merge - Access 2000 and Word 2000 DawnMKA Mailmerge 1 March 26th 05 12:20 PM
Mail Merge from Access Query KelMon Mailmerge 1 March 1st 05 02:52 AM
Mail Merge not running properly from Access Function but runs properly when opened manually Brian Alley Mailmerge 1 February 22nd 05 01:58 AM
How do I Mail Merge from an Access Query? geordymac Mailmerge 2 February 9th 05 08:59 AM
mail merge and access query DaveAS Mailmerge 6 January 6th 05 07:33 PM


All times are GMT +1. The time now is 05:04 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"