Reply
 
Thread Tools Display Modes
  #1   Report Post  
Rey
 
Posts: n/a
Default How to use access query computed fields in Word mailmerge

I just upgraded from Word 97 to Word 2003. When I try to use an Access
database in a mailmerge, the queries in Access that use computed fields using
functions that I created do not show up in the Word queries available for
mailmerge. My access databases have lots of these computed query fields.
There is probably an option somewhere that I need to set to be able to do
this but I can not find a way to do that. To reproduce this "problem" create
an access database (say TryIt.mdb)with any data in it. Then define a query
called "TestQ" One of the fields there would be Example:Mydate(). In the
common code you define a function as "
Function Mydate() as Date
Mydate = Date
end function
Now create a Word document and try to link to TryIt.mdb and the TestQ query.

Any assistance on this would be very much appreciated.
--
Rey
  #2   Report Post  
Peter Jamieson
 
Posts: n/a
Default

You have to check Word Tools|Options|General|Confirm conversion at open then
connect tot he Access database again and select the DDE option when it is
offered. Word 2002/3 changed the default method Word connects to Access from
DDE to OLEDB. OLEDB just uses the underlying Jet database engine to get the
data, and it doesn't actually know how to execute user-defined functions
created in Access. DDE starts Access, which does not how.

Peter Jamieson


"Rey" wrote in message
news
I just upgraded from Word 97 to Word 2003. When I try to use an Access
database in a mailmerge, the queries in Access that use computed fields
using
functions that I created do not show up in the Word queries available for
mailmerge. My access databases have lots of these computed query fields.
There is probably an option somewhere that I need to set to be able to do
this but I can not find a way to do that. To reproduce this "problem"
create
an access database (say TryIt.mdb)with any data in it. Then define a
query
called "TestQ" One of the fields there would be Example:Mydate(). In the
common code you define a function as "
Function Mydate() as Date
Mydate = Date
end function
Now create a Word document and try to link to TryIt.mdb and the TestQ
query.

Any assistance on this would be very much appreciated.
--
Rey



  #3   Report Post  
Rey
 
Posts: n/a
Default

Peter:
Thank you. Your reply adds some light to the problem I am
encountering. I have a followup question: Is there a way that I can specify
either in the Macro Call to do the open source
(ActiveDocument.MailMerge.OpenDataSource) or in some other parameter to use
the DDE without prompting me? The people in the church that I am writing the
code for know nothing about programming and I really do not want them to be
prompted for something they do not really understand and hence are likely to
answer the wrong thing.
Thanks,
Rey
"Peter Jamieson" wrote:

You have to check Word Tools|Options|General|Confirm conversion at open then
connect tot he Access database again and select the DDE option when it is
offered. Word 2002/3 changed the default method Word connects to Access from
DDE to OLEDB. OLEDB just uses the underlying Jet database engine to get the
data, and it doesn't actually know how to execute user-defined functions
created in Access. DDE starts Access, which does not how.

Peter Jamieson


"Rey" wrote in message
news
I just upgraded from Word 97 to Word 2003. When I try to use an Access
database in a mailmerge, the queries in Access that use computed fields
using
functions that I created do not show up in the Word queries available for
mailmerge. My access databases have lots of these computed query fields.
There is probably an option somewhere that I need to set to be able to do
this but I can not find a way to do that. To reproduce this "problem"
create
an access database (say TryIt.mdb)with any data in it. Then define a
query
called "TestQ" One of the fields there would be Example:Mydate(). In the
common code you define a function as "
Function Mydate() as Date
Mydate = Date
end function
Now create a Word document and try to link to TryIt.mdb and the TestQ
query.

Any assistance on this would be very much appreciated.
--
Rey




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

Once you have gone through the connection process once, Word should remember
the connection and your users should not have to make it (unless you are
distributing the application for use on systems where the files will be in
different folders etc., and it doesn't sound as if you are). The users may
encounter one or more security-related messages (at least one from Word
about SQL, and one from Access) but they should not have to pick a table or
query name.

To make a DDE connection in code from Word 2000/2002 I think you will need
to do at least the following:

ActiveDocument.MailMerge.OpenDataSource _
Name:="the full pathname of your access .mdb", _
Connection:="QUERY the query name", _ ' or "TABLE the table name"
SQLStatement:="SELECT * FROM [the table or query name]"

Peter Jamieson
"Rey" wrote in message
...
Peter:
Thank you. Your reply adds some light to the problem I am
encountering. I have a followup question: Is there a way that I can
specify
either in the Macro Call to do the open source
(ActiveDocument.MailMerge.OpenDataSource) or in some other parameter to
use
the DDE without prompting me? The people in the church that I am writing
the
code for know nothing about programming and I really do not want them to
be
prompted for something they do not really understand and hence are likely
to
answer the wrong thing.
Thanks,
Rey
"Peter Jamieson" wrote:

You have to check Word Tools|Options|General|Confirm conversion at open
then
connect tot he Access database again and select the DDE option when it is
offered. Word 2002/3 changed the default method Word connects to Access
from
DDE to OLEDB. OLEDB just uses the underlying Jet database engine to get
the
data, and it doesn't actually know how to execute user-defined functions
created in Access. DDE starts Access, which does not how.

Peter Jamieson


"Rey" wrote in message
news
I just upgraded from Word 97 to Word 2003. When I try to use an Access
database in a mailmerge, the queries in Access that use computed fields
using
functions that I created do not show up in the Word queries available
for
mailmerge. My access databases have lots of these computed query
fields.
There is probably an option somewhere that I need to set to be able to
do
this but I can not find a way to do that. To reproduce this "problem"
create
an access database (say TryIt.mdb)with any data in it. Then define a
query
called "TestQ" One of the fields there would be Example:Mydate(). In
the
common code you define a function as "
Function Mydate() as Date
Mydate = Date
end function
Now create a Word document and try to link to TryIt.mdb and the TestQ
query.

Any assistance on this would be very much appreciated.
--
Rey






  #5   Report Post  
Rey
 
Posts: n/a
Default

Peter:
Thanks for your helpful advice. While I was waiting for your reply I
found an even better solution (Because I do port from home to church and back
since I do the development at home). It is to use
SubType:=wdMergeSubTypeWord2000 at the end of the OpenDataSource call. I
found this answer at Cindy Meister's excellent website:
http://homepage.swissonline.ch/cindy...002/MM2002.htm

Of course, if Microsoft had documented the SubType argument to
OpenDataSource it would have saved me uncountable work and grief. But I guess
that is expecting too much. :-(
Thanks again for all your help.
Rey

"Peter Jamieson" wrote:

Once you have gone through the connection process once, Word should remember
the connection and your users should not have to make it (unless you are
distributing the application for use on systems where the files will be in
different folders etc., and it doesn't sound as if you are). The users may
encounter one or more security-related messages (at least one from Word
about SQL, and one from Access) but they should not have to pick a table or
query name.

To make a DDE connection in code from Word 2000/2002 I think you will need
to do at least the following:

ActiveDocument.MailMerge.OpenDataSource _
Name:="the full pathname of your access .mdb", _
Connection:="QUERY the query name", _ ' or "TABLE the table name"
SQLStatement:="SELECT * FROM [the table or query name]"

Peter Jamieson





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

Whoops! I'm glad you found that as it was the one thing that I left out that
really needed to be there. Time for a break, I think :-)

I have no idea why MS didn't document the SubType argument - for future
reference it's always worth
a. having a look at their knowledgebase at http://support.microsoft.com
b. searching Google Groups

Peter Jamieson
"Rey" wrote in message
...
Peter:
Thanks for your helpful advice. While I was waiting for your reply I
found an even better solution (Because I do port from home to church and
back
since I do the development at home). It is to use
SubType:=wdMergeSubTypeWord2000 at the end of the OpenDataSource call. I
found this answer at Cindy Meister's excellent website:
http://homepage.swissonline.ch/cindy...002/MM2002.htm

Of course, if Microsoft had documented the SubType argument to
OpenDataSource it would have saved me uncountable work and grief. But I
guess
that is expecting too much. :-(
Thanks again for all your help.
Rey

"Peter Jamieson" wrote:

Once you have gone through the connection process once, Word should
remember
the connection and your users should not have to make it (unless you are
distributing the application for use on systems where the files will be
in
different folders etc., and it doesn't sound as if you are). The users
may
encounter one or more security-related messages (at least one from Word
about SQL, and one from Access) but they should not have to pick a table
or
query name.

To make a DDE connection in code from Word 2000/2002 I think you will
need
to do at least the following:

ActiveDocument.MailMerge.OpenDataSource _
Name:="the full pathname of your access .mdb", _
Connection:="QUERY the query name", _ ' or "TABLE the table name"
SQLStatement:="SELECT * FROM [the table or query name]"

Peter Jamieson





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
New problem - merge Word 2002 and Access 2002 par query Brad Hays Mailmerge 1 June 1st 05 11:52 AM
Locking Two Words Together to Make a Proper Compound Noun in Word WorkingWoman Microsoft Word Help 2 April 7th 05 02:33 PM
In Word, how can I see all files (*.*) in "save as"? citizen53 New Users 8 April 4th 05 04:56 PM
letters - ask/fillin Caroline H New Users 2 February 25th 05 09:19 PM
how can you create quickwords in MS Office Word 2003 Christine S Microsoft Word Help 4 February 8th 05 04:01 PM


All times are GMT +1. The time now is 12:25 AM.

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"