[4charity]

I have successfully created an Access database that launches Word MailMerge
letters/catalogs, and populates the templates nicely, when the database is
installed on my harddrive (with templates on the server.) (It is actually a
series of 2 functions, which executes the merging of 2 different Mailmerges -
one letter, and then one email on the click of one button.)

I have now moved the database to the server, and word is launched, the
template opened, but then when it attempts to populate the fields, Word just
hangs and hangs, and eventually becomes nonresponsive. It did actually work
one time, merging the first letter, but hanging on the opening of the second
template. I know that the server is very slow, but is there anything I should
look for to try and make this work? I am posting my code he

Function MergeItInsuredOne()


Dim objWord As Word.Document
Set objWord = GetObject("X:\LCMG\Access Work\DTULetters\Insured Letter
(Full) One Invoice.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryForReport", _
SQLStatement:="SELECT * FROM [qryForReport]WHERE ClaimNumber = '" &
Me.ClaimNumberforMerge.Column(1) & "'"

' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
'close Access copy not necessary when in user access mode (Docmd.Quit)
MergeEmailInsuredFull
End Function
__________________________________________________ _____________
Function MergeEmailInsuredFull()
Dim objWord As Word.Document
Set objWord = GetObject("X:\LCMG\Access Work\DTULetters\EMail Insured
Full.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryForReport", _
SQLStatement:="SELECT * FROM [qryForReport]WHERE ClaimNumber = '" &
Me.ClaimNumberforMerge.Column(1) & "'"

' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
'Lock Document
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
NoReset:=True

End If
End Function

[Peter Jamieson]

Which version of Word/Access is this? If it is 2007, there have
definitely been reports of inconsistent responses, and I do not know the
reason.

Can you verify that Access is not displaying any dialog boxes that need
a response (e.g. use Alt-tab to look around) ?

I do not think it will work but, as an experiment, could you try
omitting the Connection:="QUERY qryForReport" parameter?

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

4charity wrote:
I have successfully created an Access database that launches Word MailMerge
letters/catalogs, and populates the templates nicely, when the database is
installed on my harddrive (with templates on the server.) (It is actually a
series of 2 functions, which executes the merging of 2 different Mailmerges -
one letter, and then one email on the click of one button.)

I have now moved the database to the server, and word is launched, the
template opened, but then when it attempts to populate the fields, Word just
hangs and hangs, and eventually becomes nonresponsive. It did actually work
one time, merging the first letter, but hanging on the opening of the second
template. I know that the server is very slow, but is there anything I should
look for to try and make this work? I am posting my code he

Function MergeItInsuredOne()


Dim objWord As Word.Document
Set objWord = GetObject("X:\LCMG\Access Work\DTULetters\Insured Letter
(Full) One Invoice.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryForReport", _
SQLStatement:="SELECT * FROM [qryForReport]WHERE ClaimNumber = '" &
Me.ClaimNumberforMerge.Column(1) & "'"

' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
'close Access copy not necessary when in user access mode (Docmd.Quit)
MergeEmailInsuredFull
End Function
__________________________________________________ _____________
Function MergeEmailInsuredFull()
Dim objWord As Word.Document
Set objWord = GetObject("X:\LCMG\Access Work\DTULetters\EMail Insured
Full.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryForReport", _
SQLStatement:="SELECT * FROM [qryForReport]WHERE ClaimNumber = '" &
Me.ClaimNumberforMerge.Column(1) & "'"

' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
'Lock Document
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
NoReset:=True

End If
End Function

[4charity]

This is Access 2002.

There are no dialog boxes, and actually, I have now split the database, and
have kept the Word templates and the Access frontend locally on my machine,
and moved the backend (tables & queries) to the server. This has helped
somewhat, as the application now does complete successfully, although it
takes 1 minute 20 seconds to complete the merge, in the early morning when
only a few people are accessing the server.

I have tried removing the statement, as you suggested, and get a compile
error message from Access, "Expected: name parameter".

Any other thoughts would be greatly appreciated.
Thanks.

"Peter Jamieson" wrote:

Which version of Word/Access is this? If it is 2007, there have
definitely been reports of inconsistent responses, and I do not know the
reason.

Can you verify that Access is not displaying any dialog boxes that need
a response (e.g. use Alt-tab to look around) ?

I do not think it will work but, as an experiment, could you try
omitting the Connection:="QUERY qryForReport" parameter?

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

4charity wrote:
I have successfully created an Access database that launches Word MailMerge
letters/catalogs, and populates the templates nicely, when the database is
installed on my harddrive (with templates on the server.) (It is actually a
series of 2 functions, which executes the merging of 2 different Mailmerges -
one letter, and then one email on the click of one button.)

I have now moved the database to the server, and word is launched, the
template opened, but then when it attempts to populate the fields, Word just
hangs and hangs, and eventually becomes nonresponsive. It did actually work
one time, merging the first letter, but hanging on the opening of the second
template. I know that the server is very slow, but is there anything I should
look for to try and make this work? I am posting my code he

Function MergeItInsuredOne()


Dim objWord As Word.Document
Set objWord = GetObject("X:\LCMG\Access Work\DTULetters\Insured Letter
(Full) One Invoice.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryForReport", _
SQLStatement:="SELECT * FROM [qryForReport]WHERE ClaimNumber = '" &
Me.ClaimNumberforMerge.Column(1) & "'"

' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
'close Access copy not necessary when in user access mode (Docmd.Quit)
MergeEmailInsuredFull
End Function
__________________________________________________ _____________
Function MergeEmailInsuredFull()
Dim objWord As Word.Document
Set objWord = GetObject("X:\LCMG\Access Work\DTULetters\EMail Insured
Full.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryForReport", _
SQLStatement:="SELECT * FROM [qryForReport]WHERE ClaimNumber = '" &
Me.ClaimNumberforMerge.Column(1) & "'"

' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
'Lock Document
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
NoReset:=True

End If
End Function

[Peter Jamieson]

1. I suspect that the performance is more to do with the way that Access
gets data over a network.

2. As far as the experiment is concerned,

I have tried removing the statement, as you suggested, and get a compile
error message from Access, "Expected: name parameter".

can you try this again, because I don't get this particular problem.
The first OpenDataSource call should look something like...

objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
SQLStatement:= _
" SELECT * FROM [qryForReport]" & _
" WHERE ClaimNumber = '" & _
Me.ClaimNumberforMerge.Column(1) & "'"

(BTW I have introduced a space between the "]" and the "WHERE" there,
although it sounds as if it works OK without it)

What I am trying to do here is change the way that Word connects to
Access to be the default method, which is OLE DB. With a Connection
parameter set to "QUERY qryForReport" I am pretty sure that Word will be
using DDE. However,
a. if qryForReport is a type of query that OLE DB cannot 'see" (e.g. a
query that invokes an Access user-defined VBA function), then this will
not work anyway
b. since your code is actually running in Access, it can be difficult
to test OLE DB connections because if you have the Module or Macro
editor open, the database is locked and Word will not open the data source.

Up to you whether you think it's worth pursuing.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

4charity wrote:
This is Access 2002.

There are no dialog boxes, and actually, I have now split the database, and
have kept the Word templates and the Access frontend locally on my machine,
and moved the backend (tables & queries) to the server. This has helped
somewhat, as the application now does complete successfully, although it
takes 1 minute 20 seconds to complete the merge, in the early morning when
only a few people are accessing the server.

I have tried removing the statement, as you suggested, and get a compile
error message from Access, "Expected: name parameter".

Any other thoughts would be greatly appreciated.
Thanks.

"Peter Jamieson" wrote:

Which version of Word/Access is this? If it is 2007, there have
definitely been reports of inconsistent responses, and I do not know the
reason.

Can you verify that Access is not displaying any dialog boxes that need
a response (e.g. use Alt-tab to look around) ?

I do not think it will work but, as an experiment, could you try
omitting the Connection:="QUERY qryForReport" parameter?

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

4charity wrote:
I have successfully created an Access database that launches Word MailMerge
letters/catalogs, and populates the templates nicely, when the database is
installed on my harddrive (with templates on the server.) (It is actually a
series of 2 functions, which executes the merging of 2 different Mailmerges -
one letter, and then one email on the click of one button.)

I have now moved the database to the server, and word is launched, the
template opened, but then when it attempts to populate the fields, Word just
hangs and hangs, and eventually becomes nonresponsive. It did actually work
one time, merging the first letter, but hanging on the opening of the second
template. I know that the server is very slow, but is there anything I should
look for to try and make this work? I am posting my code he

Function MergeItInsuredOne()


Dim objWord As Word.Document
Set objWord = GetObject("X:\LCMG\Access Work\DTULetters\Insured Letter
(Full) One Invoice.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryForReport", _
SQLStatement:="SELECT * FROM [qryForReport]WHERE ClaimNumber = '" &
Me.ClaimNumberforMerge.Column(1) & "'"

' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
'close Access copy not necessary when in user access mode (Docmd.Quit)
MergeEmailInsuredFull
End Function
__________________________________________________ _____________
Function MergeEmailInsuredFull()
Dim objWord As Word.Document
Set objWord = GetObject("X:\LCMG\Access Work\DTULetters\EMail Insured
Full.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryForReport", _
SQLStatement:="SELECT * FROM [qryForReport]WHERE ClaimNumber = '" &
Me.ClaimNumberforMerge.Column(1) & "'"

' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
'Lock Document
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
NoReset:=True

End If
End Function

[4charity]

Thanks, Peter.

I replaced the code in both parts of the process per your suggestion, and
the program works successfully. However, the run time is the same as
previously. I have been using a stopwatch to time it both ways, and they are
just about identical (a few seconds one way or the other, because of user
variable.)

I have mapped out the path to the final query that is used to get to the
letters - and have found that it is quite tortuous indeed - utilizes 8
queries and multiple tables to get there, does about 20 calculations along
the way. I am trying to pare down to only the actual info that is needed, and
possibly eliminate/combine some of the queries......... but don't know how
much I will be able to minimize. There is, for instance a query written in
SQL that calculates the running totals of several fields. And there are other
queries that just calculate the SumOf or MinOf other fields.

Any other thoughts are still greatly appreciated.
Thanks for your time so far on this.

"Peter Jamieson" wrote:

1. I suspect that the performance is more to do with the way that Access
gets data over a network.

2. As far as the experiment is concerned,

I have tried removing the statement, as you suggested, and get a compile
error message from Access, "Expected: name parameter".

can you try this again, because I don't get this particular problem.
The first OpenDataSource call should look something like...

objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
SQLStatement:= _
" SELECT * FROM [qryForReport]" & _
" WHERE ClaimNumber = '" & _
Me.ClaimNumberforMerge.Column(1) & "'"

(BTW I have introduced a space between the "]" and the "WHERE" there,
although it sounds as if it works OK without it)

What I am trying to do here is change the way that Word connects to
Access to be the default method, which is OLE DB. With a Connection
parameter set to "QUERY qryForReport" I am pretty sure that Word will be
using DDE. However,
a. if qryForReport is a type of query that OLE DB cannot 'see" (e.g. a
query that invokes an Access user-defined VBA function), then this will
not work anyway
b. since your code is actually running in Access, it can be difficult
to test OLE DB connections because if you have the Module or Macro
editor open, the database is locked and Word will not open the data source.

Up to you whether you think it's worth pursuing.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

4charity wrote:
This is Access 2002.

There are no dialog boxes, and actually, I have now split the database, and
have kept the Word templates and the Access frontend locally on my machine,
and moved the backend (tables & queries) to the server. This has helped
somewhat, as the application now does complete successfully, although it
takes 1 minute 20 seconds to complete the merge, in the early morning when
only a few people are accessing the server.

I have tried removing the statement, as you suggested, and get a compile
error message from Access, "Expected: name parameter".

Any other thoughts would be greatly appreciated.
Thanks.

"Peter Jamieson" wrote:

Which version of Word/Access is this? If it is 2007, there have
definitely been reports of inconsistent responses, and I do not know the
reason.

Can you verify that Access is not displaying any dialog boxes that need
a response (e.g. use Alt-tab to look around) ?

I do not think it will work but, as an experiment, could you try
omitting the Connection:="QUERY qryForReport" parameter?

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

4charity wrote:
I have successfully created an Access database that launches Word MailMerge
letters/catalogs, and populates the templates nicely, when the database is
installed on my harddrive (with templates on the server.) (It is actually a
series of 2 functions, which executes the merging of 2 different Mailmerges -
one letter, and then one email on the click of one button.)

I have now moved the database to the server, and word is launched, the
template opened, but then when it attempts to populate the fields, Word just
hangs and hangs, and eventually becomes nonresponsive. It did actually work
one time, merging the first letter, but hanging on the opening of the second
template. I know that the server is very slow, but is there anything I should
look for to try and make this work? I am posting my code he

Function MergeItInsuredOne()


Dim objWord As Word.Document
Set objWord = GetObject("X:\LCMG\Access Work\DTULetters\Insured Letter
(Full) One Invoice.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryForReport", _
SQLStatement:="SELECT * FROM [qryForReport]WHERE ClaimNumber = '" &
Me.ClaimNumberforMerge.Column(1) & "'"

' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
'close Access copy not necessary when in user access mode (Docmd.Quit)
MergeEmailInsuredFull
End Function
__________________________________________________ _____________
Function MergeEmailInsuredFull()
Dim objWord As Word.Document
Set objWord = GetObject("X:\LCMG\Access Work\DTULetters\EMail Insured
Full.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryForReport", _
SQLStatement:="SELECT * FROM [qryForReport]WHERE ClaimNumber = '" &
Me.ClaimNumberforMerge.Column(1) & "'"

' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
'Lock Document
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
NoReset:=True

End If
End Function

[Peter Jamieson]

You're more likely to benefit from an Access group when it comes to
restructuring a complex query. All I know is that the performance of
queries depends to a large extent on whether, for example, a join is
performed on the client side (in which case the client will probably
have to retrieve all candidate records for the join, even if it then
discards 99% of them), or on the server side, incurring no network
traffic and potentially benefitting from caching on the server. There
are probably also numerous MS support articles on this subject.


Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

4charity wrote:
Thanks, Peter.

I replaced the code in both parts of the process per your suggestion, and
the program works successfully. However, the run time is the same as
previously. I have been using a stopwatch to time it both ways, and they are
just about identical (a few seconds one way or the other, because of user
variable.)

I have mapped out the path to the final query that is used to get to the
letters - and have found that it is quite tortuous indeed - utilizes 8
queries and multiple tables to get there, does about 20 calculations along
the way. I am trying to pare down to only the actual info that is needed, and
possibly eliminate/combine some of the queries......... but don't know how
much I will be able to minimize. There is, for instance a query written in
SQL that calculates the running totals of several fields. And there are other
queries that just calculate the SumOf or MinOf other fields.

Any other thoughts are still greatly appreciated.
Thanks for your time so far on this.

"Peter Jamieson" wrote:

1. I suspect that the performance is more to do with the way that Access
gets data over a network.

2. As far as the experiment is concerned,

I have tried removing the statement, as you suggested, and get a compile
error message from Access, "Expected: name parameter".

can you try this again, because I don't get this particular problem.
The first OpenDataSource call should look something like...

objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
SQLStatement:= _
" SELECT * FROM [qryForReport]" & _
" WHERE ClaimNumber = '" & _
Me.ClaimNumberforMerge.Column(1) & "'"

(BTW I have introduced a space between the "]" and the "WHERE" there,
although it sounds as if it works OK without it)

What I am trying to do here is change the way that Word connects to
Access to be the default method, which is OLE DB. With a Connection
parameter set to "QUERY qryForReport" I am pretty sure that Word will be
using DDE. However,
a. if qryForReport is a type of query that OLE DB cannot 'see" (e.g. a
query that invokes an Access user-defined VBA function), then this will
not work anyway
b. since your code is actually running in Access, it can be difficult
to test OLE DB connections because if you have the Module or Macro
editor open, the database is locked and Word will not open the data source.

Up to you whether you think it's worth pursuing.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

4charity wrote:
This is Access 2002.

There are no dialog boxes, and actually, I have now split the database, and
have kept the Word templates and the Access frontend locally on my machine,
and moved the backend (tables & queries) to the server. This has helped
somewhat, as the application now does complete successfully, although it
takes 1 minute 20 seconds to complete the merge, in the early morning when
only a few people are accessing the server.

I have tried removing the statement, as you suggested, and get a compile
error message from Access, "Expected: name parameter".

Any other thoughts would be greatly appreciated.
Thanks.

"Peter Jamieson" wrote:

Which version of Word/Access is this? If it is 2007, there have
definitely been reports of inconsistent responses, and I do not know the
reason.

Can you verify that Access is not displaying any dialog boxes that need
a response (e.g. use Alt-tab to look around) ?

I do not think it will work but, as an experiment, could you try
omitting the Connection:="QUERY qryForReport" parameter?

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

4charity wrote:
I have successfully created an Access database that launches Word MailMerge
letters/catalogs, and populates the templates nicely, when the database is
installed on my harddrive (with templates on the server.) (It is actually a
series of 2 functions, which executes the merging of 2 different Mailmerges -
one letter, and then one email on the click of one button.)

I have now moved the database to the server, and word is launched, the
template opened, but then when it attempts to populate the fields, Word just
hangs and hangs, and eventually becomes nonresponsive. It did actually work
one time, merging the first letter, but hanging on the opening of the second
template. I know that the server is very slow, but is there anything I should
look for to try and make this work? I am posting my code he

Function MergeItInsuredOne()


Dim objWord As Word.Document
Set objWord = GetObject("X:\LCMG\Access Work\DTULetters\Insured Letter
(Full) One Invoice.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryForReport", _
SQLStatement:="SELECT * FROM [qryForReport]WHERE ClaimNumber = '" &
Me.ClaimNumberforMerge.Column(1) & "'"

' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
'close Access copy not necessary when in user access mode (Docmd.Quit)
MergeEmailInsuredFull
End Function
__________________________________________________ _____________
Function MergeEmailInsuredFull()
Dim objWord As Word.Document
Set objWord = GetObject("X:\LCMG\Access Work\DTULetters\EMail Insured
Full.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryForReport", _
SQLStatement:="SELECT * FROM [qryForReport]WHERE ClaimNumber = '" &
Me.ClaimNumberforMerge.Column(1) & "'"

' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
'Lock Document
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
NoReset:=True

End If
End Function

[4charity]

Thanks again, Peter.

I will continue to do more research into it. Since I am fairly new to
working in this environment, I am not sure what you mean by an "Access group"
with help to restructuring the query? Meanwhile, management is not too
worried about the delay time in the process, seeing that it does work, and is
accurate. Hopefully I will be able to make some more efficiency adjustments;
and from what I understand from our processors, they are used to lag time in
their processing.... sounds like its time for a server upgrade!

"Peter Jamieson" wrote:

You're more likely to benefit from an Access group when it comes to
restructuring a complex query. All I know is that the performance of
queries depends to a large extent on whether, for example, a join is
performed on the client side (in which case the client will probably
have to retrieve all candidate records for the join, even if it then
discards 99% of them), or on the server side, incurring no network
traffic and potentially benefitting from caching on the server. There
are probably also numerous MS support articles on this subject.


Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

4charity wrote:
Thanks, Peter.

I replaced the code in both parts of the process per your suggestion, and
the program works successfully. However, the run time is the same as
previously. I have been using a stopwatch to time it both ways, and they are
just about identical (a few seconds one way or the other, because of user
variable.)

I have mapped out the path to the final query that is used to get to the
letters - and have found that it is quite tortuous indeed - utilizes 8
queries and multiple tables to get there, does about 20 calculations along
the way. I am trying to pare down to only the actual info that is needed, and
possibly eliminate/combine some of the queries......... but don't know how
much I will be able to minimize. There is, for instance a query written in
SQL that calculates the running totals of several fields. And there are other
queries that just calculate the SumOf or MinOf other fields.

Any other thoughts are still greatly appreciated.
Thanks for your time so far on this.

"Peter Jamieson" wrote:

1. I suspect that the performance is more to do with the way that Access
gets data over a network.

2. As far as the experiment is concerned,

I have tried removing the statement, as you suggested, and get a compile
error message from Access, "Expected: name parameter".

can you try this again, because I don't get this particular problem.
The first OpenDataSource call should look something like...

objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
SQLStatement:= _
" SELECT * FROM [qryForReport]" & _
" WHERE ClaimNumber = '" & _
Me.ClaimNumberforMerge.Column(1) & "'"

(BTW I have introduced a space between the "]" and the "WHERE" there,
although it sounds as if it works OK without it)

What I am trying to do here is change the way that Word connects to
Access to be the default method, which is OLE DB. With a Connection
parameter set to "QUERY qryForReport" I am pretty sure that Word will be
using DDE. However,
a. if qryForReport is a type of query that OLE DB cannot 'see" (e.g. a
query that invokes an Access user-defined VBA function), then this will
not work anyway
b. since your code is actually running in Access, it can be difficult
to test OLE DB connections because if you have the Module or Macro
editor open, the database is locked and Word will not open the data source.

Up to you whether you think it's worth pursuing.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

4charity wrote:
This is Access 2002.

There are no dialog boxes, and actually, I have now split the database, and
have kept the Word templates and the Access frontend locally on my machine,
and moved the backend (tables & queries) to the server. This has helped
somewhat, as the application now does complete successfully, although it
takes 1 minute 20 seconds to complete the merge, in the early morning when
only a few people are accessing the server.

I have tried removing the statement, as you suggested, and get a compile
error message from Access, "Expected: name parameter".

Any other thoughts would be greatly appreciated.
Thanks.

"Peter Jamieson" wrote:

Which version of Word/Access is this? If it is 2007, there have
definitely been reports of inconsistent responses, and I do not know the
reason.

Can you verify that Access is not displaying any dialog boxes that need
a response (e.g. use Alt-tab to look around) ?

I do not think it will work but, as an experiment, could you try
omitting the Connection:="QUERY qryForReport" parameter?

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

4charity wrote:
I have successfully created an Access database that launches Word MailMerge
letters/catalogs, and populates the templates nicely, when the database is
installed on my harddrive (with templates on the server.) (It is actually a
series of 2 functions, which executes the merging of 2 different Mailmerges -
one letter, and then one email on the click of one button.)

I have now moved the database to the server, and word is launched, the
template opened, but then when it attempts to populate the fields, Word just
hangs and hangs, and eventually becomes nonresponsive. It did actually work
one time, merging the first letter, but hanging on the opening of the second
template. I know that the server is very slow, but is there anything I should
look for to try and make this work? I am posting my code he

Function MergeItInsuredOne()


Dim objWord As Word.Document
Set objWord = GetObject("X:\LCMG\Access Work\DTULetters\Insured Letter
(Full) One Invoice.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryForReport", _
SQLStatement:="SELECT * FROM [qryForReport]WHERE ClaimNumber = '" &
Me.ClaimNumberforMerge.Column(1) & "'"

' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
'close Access copy not necessary when in user access mode (Docmd.Quit)
MergeEmailInsuredFull
End Function
__________________________________________________ _____________
Function MergeEmailInsuredFull()
Dim objWord As Word.Document
Set objWord = GetObject("X:\LCMG\Access Work\DTULetters\EMail Insured
Full.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryForReport", _
SQLStatement:="SELECT * FROM [qryForReport]WHERE ClaimNumber = '" &
Me.ClaimNumberforMerge.Column(1) & "'"

' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
'Lock Document
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
NoReset:=True

End If
End Function

[Peter Jamieson]

Last question first...

Meanwhile, management is not too
worried about the delay time in the process, seeing that it does
work, and is accurate.

Sounds like good management to me. Not my business, but perhaps better
to "put this on the back burner" for now and pay attention to other
priorities :-)

Since I am fairly new to
working in this environment, I am not sure what you mean by an "Access
group"
with help to restructuring the query?

Well, there are various ways to post questions here, - from my point of
view, your question ended up in a /Usenet Newsgroup/ called
nntp://microsoft.public.word.mailmerge.fields

(I think!)

But there are various ways to post questions in this newsgroup,
including Microsoft's own web-based interface at e.g.

http://www.microsoft.com/office/comm...erview.mspx#13

You'll find groups in there that are much more clued-up on Access
issues, which IMO is a good place to start. Let them know what
version(s) of Access (and Windows) you're using, how your Access
application is structured and provide whatever Access VBA/Jet SQL code
you can.

My only reservation is that Word mailmerge issues and Access issues are
often cross-platform issues, and I suspect the number of people who have
a good grasp of how the two products work together is quite small.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

4charity wrote:
Thanks again, Peter.

I will continue to do more research into it. Since I am fairly new to
working in this environment, I am not sure what you mean by an "Access group"
with help to restructuring the query? Meanwhile, management is not too
worried about the delay time in the process, seeing that it does work, and is
accurate. Hopefully I will be able to make some more efficiency adjustments;
and from what I understand from our processors, they are used to lag time in
their processing.... sounds like its time for a server upgrade!

"Peter Jamieson" wrote:

You're more likely to benefit from an Access group when it comes to
restructuring a complex query. All I know is that the performance of
queries depends to a large extent on whether, for example, a join is
performed on the client side (in which case the client will probably
have to retrieve all candidate records for the join, even if it then
discards 99% of them), or on the server side, incurring no network
traffic and potentially benefitting from caching on the server. There
are probably also numerous MS support articles on this subject.


Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

4charity wrote:
Thanks, Peter.

I replaced the code in both parts of the process per your suggestion, and
the program works successfully. However, the run time is the same as
previously. I have been using a stopwatch to time it both ways, and they are
just about identical (a few seconds one way or the other, because of user
variable.)

I have mapped out the path to the final query that is used to get to the
letters - and have found that it is quite tortuous indeed - utilizes 8
queries and multiple tables to get there, does about 20 calculations along
the way. I am trying to pare down to only the actual info that is needed, and
possibly eliminate/combine some of the queries......... but don't know how
much I will be able to minimize. There is, for instance a query written in
SQL that calculates the running totals of several fields. And there are other
queries that just calculate the SumOf or MinOf other fields.

Any other thoughts are still greatly appreciated.
Thanks for your time so far on this.

"Peter Jamieson" wrote:

1. I suspect that the performance is more to do with the way that Access
gets data over a network.

2. As far as the experiment is concerned,

I have tried removing the statement, as you suggested, and get a compile
error message from Access, "Expected: name parameter".

can you try this again, because I don't get this particular problem.
The first OpenDataSource call should look something like...

objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
SQLStatement:= _
" SELECT * FROM [qryForReport]" & _
" WHERE ClaimNumber = '" & _
Me.ClaimNumberforMerge.Column(1) & "'"

(BTW I have introduced a space between the "]" and the "WHERE" there,
although it sounds as if it works OK without it)

What I am trying to do here is change the way that Word connects to
Access to be the default method, which is OLE DB. With a Connection
parameter set to "QUERY qryForReport" I am pretty sure that Word will be
using DDE. However,
a. if qryForReport is a type of query that OLE DB cannot 'see" (e.g. a
query that invokes an Access user-defined VBA function), then this will
not work anyway
b. since your code is actually running in Access, it can be difficult
to test OLE DB connections because if you have the Module or Macro
editor open, the database is locked and Word will not open the data source.

Up to you whether you think it's worth pursuing.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

4charity wrote:
This is Access 2002.

There are no dialog boxes, and actually, I have now split the database, and
have kept the Word templates and the Access frontend locally on my machine,
and moved the backend (tables & queries) to the server. This has helped
somewhat, as the application now does complete successfully, although it
takes 1 minute 20 seconds to complete the merge, in the early morning when
only a few people are accessing the server.

I have tried removing the statement, as you suggested, and get a compile
error message from Access, "Expected: name parameter".

Any other thoughts would be greatly appreciated.
Thanks.

"Peter Jamieson" wrote:

Which version of Word/Access is this? If it is 2007, there have
definitely been reports of inconsistent responses, and I do not know the
reason.

Can you verify that Access is not displaying any dialog boxes that need
a response (e.g. use Alt-tab to look around) ?

I do not think it will work but, as an experiment, could you try
omitting the Connection:="QUERY qryForReport" parameter?

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

4charity wrote:
I have successfully created an Access database that launches Word MailMerge
letters/catalogs, and populates the templates nicely, when the database is
installed on my harddrive (with templates on the server.) (It is actually a
series of 2 functions, which executes the merging of 2 different Mailmerges -
one letter, and then one email on the click of one button.)

I have now moved the database to the server, and word is launched, the
template opened, but then when it attempts to populate the fields, Word just
hangs and hangs, and eventually becomes nonresponsive. It did actually work
one time, merging the first letter, but hanging on the opening of the second
template. I know that the server is very slow, but is there anything I should
look for to try and make this work? I am posting my code he

Function MergeItInsuredOne()


Dim objWord As Word.Document
Set objWord = GetObject("X:\LCMG\Access Work\DTULetters\Insured Letter
(Full) One Invoice.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryForReport", _
SQLStatement:="SELECT * FROM [qryForReport]WHERE ClaimNumber = '" &
Me.ClaimNumberforMerge.Column(1) & "'"

' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
'close Access copy not necessary when in user access mode (Docmd.Quit)
MergeEmailInsuredFull
End Function
__________________________________________________ _____________
Function MergeEmailInsuredFull()
Dim objWord As Word.Document
Set objWord = GetObject("X:\LCMG\Access Work\DTULetters\EMail Insured
Full.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="X:\LCMG\DTU\DTDBeta.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryForReport", _
SQLStatement:="SELECT * FROM [qryForReport]WHERE ClaimNumber = '" &
Me.ClaimNumberforMerge.Column(1) & "'"

' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
'Lock Document
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
NoReset:=True

End If
End Function