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