Access to Word mailmerge staggeringly slow

I'm in the process of upgrading an Access 97 application to Access 2003. This
has mostly been pretty smooth, but I'm having major problems with a process
that generates mailmerge documents (also moving from Word 97 to Word 2003).

Taking a specific example, my initial connection string (Access 97 to Word
97) was:

objDoc.MailMerge.OpenDataSource Name:=CurrentDb.Name, OpenExclusive:=False, 
LinkToSource:=True, _
AddToRecentFiles:=False, Connection:="TABLE " & strTableName

This wasn't working at all in Access 2003 - every time it tried, it popped
up a dialog box asking me to select the table. I found the reason for that in
this article (http://support.microsoft.com/kb/289830), and
therefore changed my code to:

objDoc.MailMerge.OpenDataSource Name:=CurrentDb.Name, OpenExclusive:=False, 
LinkToSource:=True, _
AddToRecentFiles:=False, SQLStatement:="Select * From " & strTableName, _ 
SubType:=wdMergeSubTypeWord2000

This now works in Access 2003, but it's appallingly slow. Whereas before it
would take maybe five seconds to apply the connection string and execute the
mailmerge, it is now taking up to two minutes. This is bad enough for one
document, but this is part of a batch process that runs overnight and
involves several hundred mail merges.

This looks like it might put the kibosh on us upgrading to 2003, which would
be a real nuisance. Does anyone have any way around it?

Thanks...

Try using the first (SQL Statement:= ) method in that knowledge base
article.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"InvisibleDuncan" wrote in
message ...
I'm in the process of upgrading an Access 97 application to Access 2003.
This
has mostly been pretty smooth, but I'm having major problems with a
process
that generates mailmerge documents (also moving from Word 97 to Word
2003).

Taking a specific example, my initial connection string (Access 97 to Word
97) was:

 objDoc.MailMerge.OpenDataSource Name:=CurrentDb.Name, 
 OpenExclusive:=False,
 LinkToSource:=True, _
 AddToRecentFiles:=False, Connection:="TABLE " & strTableName

This wasn't working at all in Access 2003 - every time it tried, it popped
up a dialog box asking me to select the table. I found the reason for that
in
this article (http://support.microsoft.com/kb/289830), and
therefore changed my code to:

 objDoc.MailMerge.OpenDataSource Name:=CurrentDb.Name, 
 OpenExclusive:=False,
 LinkToSource:=True, _
 AddToRecentFiles:=False, SQLStatement:="Select * From " & strTableName, _
 SubType:=wdMergeSubTypeWord2000

This now works in Access 2003, but it's appallingly slow. Whereas before
it
would take maybe five seconds to apply the connection string and execute
the
mailmerge, it is now taking up to two minutes. This is bad enough for one
document, but this is part of a batch process that runs overnight and
involves several hundred mail merges.

This looks like it might put the kibosh on us upgrading to 2003, which
would
be a real nuisance. Does anyone have any way around it?

Thanks...

Thanks for the suggestion, Doug, but I do already use that statement in my
revised code, as in the example I posted originally. Using that statement
allows it to run without prompting for a table, but it's one of the things
that makes it really slow. It's the speed I'm having issues with at the
moment.

Cheers,

--
Duncan

"Doug Robbins - Word MVP" wrote:

Try using the first (SQL Statement:= ) method in that knowledge base
article.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

To do "example 1" you have to

/include/

SQLStatement:="Select * From " & strTableName, _

and /omit/

SubType:=wdMergeSubTypeWord2000

and personally I would consider either removing the COnnection parameter
altogether or setting it to ""

Example 1 should connect using OLE DB

Example 2 should connect using DDE, which is likely the cause of your
problem.

Peter Jamieson

http://tips.pjmsn.me.uk

On 22/02/2010 09:22, InvisibleDuncan wrote:
Thanks for the suggestion, Doug, but I do already use that statement in my
revised code, as in the example I posted originally. Using that statement
allows it to run without prompting for a table, but it's one of the things
that makes it really slow. It's the speed I'm having issues with at the
moment.

Cheers,

--
Duncan

"Doug Robbins - Word MVP" wrote:

Try using the first (SQL Statement:= ) method in that knowledge base
article.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Maybe I really meant to suggest trying the other method (DDE)

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"InvisibleDuncan" wrote in
message ...
Thanks for the suggestion, Doug, but I do already use that statement in my
revised code, as in the example I posted originally. Using that statement
allows it to run without prompting for a table, but it's one of the things
that makes it really slow. It's the speed I'm having issues with at the
moment.

Cheers,

--
Duncan

"Doug Robbins - Word MVP" wrote:

Try using the first (SQL Statement:= ) method in that knowledge base
article.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Thank you both for the help so far, but unfortunately it hasn't managed to
solve the problem. Including 'SQLStatement:="Select * From " & strTableName'
and omitting 'SubType:=wdMergeSubTypeWord2000' gives me a different error:
"This operation cannot be completed because of dialog or database engine
failures. Please try again later."

Using the combination of the two is the only way I have found of getting it
work at all between Access and Word 2003, but this is unfortunately causing
the ridiculous slowness. I presume it is because it's now using DDE instead
of OLE DB, but I can't find a way around it.

"Peter Jamieson" wrote:

To do "example 1" you have to

/include/

SQLStatement:="Select * From " & strTableName, _

and /omit/

SubType:=wdMergeSubTypeWord2000

and personally I would consider either removing the COnnection parameter
altogether or setting it to ""

Example 1 should connect using OLE DB

Example 2 should connect using DDE, which is likely the cause of your
problem.

Peter Jamieson

http://tips.pjmsn.me.uk

On 22/02/2010 09:22, InvisibleDuncan wrote:
Thanks for the suggestion, Doug, but I do already use that statement in my
revised code, as in the example I posted originally. Using that statement
allows it to run without prompting for a table, but it's one of the things
that makes it really slow. It's the speed I'm having issues with at the
moment.

Cheers,

--
Duncan

"Doug Robbins - Word MVP" wrote:

Try using the first (SQL Statement:= ) method in that knowledge base
article.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com


.

Something that I have never understood is that sometimes when you have
the Access VBA Editor open, or run Access VBA code, Access seems to put
the database in a state that makes connections from Word fail. Ten
minutes later you can run the same code and it works fine. I can only
assume that it has something to do with the locking and/or timeout
options in Access Tools-Options-Advanced. Or perhaps it's just some
simple thing I'm failing to do, like stop debugging or some such.

It isn't something I have explored in depth, but it's exactly what just
happened when trying to replicate your problem. BTW, I was able to
connect via OLEDB using the minimalist

objDoc.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
sqlstatement:="SELECT * FROM [Table1]"


Peter Jamieson

http://tips.pjmsn.me.uk

On 22/02/2010 10:53, InvisibleDuncan wrote:
Thank you both for the help so far, but unfortunately it hasn't managed to
solve the problem. Including 'SQLStatement:="Select * From "& strTableName'
and omitting 'SubType:=wdMergeSubTypeWord2000' gives me a different error:
"This operation cannot be completed because of dialog or database engine
failures. Please try again later."

Using the combination of the two is the only way I have found of getting it
work at all between Access and Word 2003, but this is unfortunately causing
the ridiculous slowness. I presume it is because it's now using DDE instead
of OLE DB, but I can't find a way around it.

"Peter Jamieson" wrote:

To do "example 1" you have to

/include/

SQLStatement:="Select * From "& strTableName, _

and /omit/

SubType:=wdMergeSubTypeWord2000

and personally I would consider either removing the COnnection parameter
altogether or setting it to ""

Example 1 should connect using OLE DB

Example 2 should connect using DDE, which is likely the cause of your
problem.

Peter Jamieson

http://tips.pjmsn.me.uk

On 22/02/2010 09:22, InvisibleDuncan wrote:
Thanks for the suggestion, Doug, but I do already use that statement in my
revised code, as in the example I posted originally. Using that statement
allows it to run without prompting for a table, but it's one of the things
that makes it really slow. It's the speed I'm having issues with at the
moment.

Cheers,

--
Duncan

"Doug Robbins - Word MVP" wrote:

Try using the first (SQL Statement:= ) method in that knowledge base
article.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com


.

According to a trawl of the net, it would seem that the reason for the error
I get when trying to use OLEDB (the "dialog or database engine" error) is
that my database, although not opened exclusively, does something that makes
it act as if it's open exclusively, and it therefore prevents Word from
accessing it. I think that probably fits in with what you're saying. The
advice was to open it again from within the Access code itself (doing a
Connection.Open), as that would force it to be opened in shared mode. Sadly,
it doesn't work for me.

I get the same error when using your minimalist version of the connection
string, too.

"Peter Jamieson" wrote:

Something that I have never understood is that sometimes when you have
the Access VBA Editor open, or run Access VBA code, Access seems to put
the database in a state that makes connections from Word fail. Ten
minutes later you can run the same code and it works fine. I can only
assume that it has something to do with the locking and/or timeout
options in Access Tools-Options-Advanced. Or perhaps it's just some
simple thing I'm failing to do, like stop debugging or some such.

It isn't something I have explored in depth, but it's exactly what just
happened when trying to replicate your problem. BTW, I was able to
connect via OLEDB using the minimalist

objDoc.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
sqlstatement:="SELECT * FROM [Table1]"


Peter Jamieson

http://tips.pjmsn.me.uk

The only other suggestion I can make may not be feasible in your
situation, and that is of course that if you have split
forms/macros/code into a separate front end and data into a separate
back end, then maybe there is not such a problem in this area.

It may be worth asking a new question in an access group about the whole
issue of what exactly Access is locking that causes other stuff to fail
- I certainly don't have the experience with Access to have much of a
clue other than what I have already said.

Peter Jamieson

http://tips.pjmsn.me.uk

On 22/02/2010 13:45, InvisibleDuncan wrote:
According to a trawl of the net, it would seem that the reason for the error
I get when trying to use OLEDB (the "dialog or database engine" error) is
that my database, although not opened exclusively, does something that makes
it act as if it's open exclusively, and it therefore prevents Word from
accessing it. I think that probably fits in with what you're saying. The
advice was to open it again from within the Access code itself (doing a
Connection.Open), as that would force it to be opened in shared mode. Sadly,
it doesn't work for me.

I get the same error when using your minimalist version of the connection
string, too.

"Peter Jamieson" wrote:

Something that I have never understood is that sometimes when you have
the Access VBA Editor open, or run Access VBA code, Access seems to put
the database in a state that makes connections from Word fail. Ten
minutes later you can run the same code and it works fine. I can only
assume that it has something to do with the locking and/or timeout
options in Access Tools-Options-Advanced. Or perhaps it's just some
simple thing I'm failing to do, like stop debugging or some such.

It isn't something I have explored in depth, but it's exactly what just
happened when trying to replicate your problem. BTW, I was able to
connect via OLEDB using the minimalist

objDoc.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
sqlstatement:="SELECT * FROM [Table1]"


Peter Jamieson

http://tips.pjmsn.me.uk

Yes, I think that might be a wise move. I'll give the Access experts a go!

Thanks for all the help.

--
Duncan

"Peter Jamieson" wrote:

The only other suggestion I can make may not be feasible in your
situation, and that is of course that if you have split
forms/macros/code into a separate front end and data into a separate
back end, then maybe there is not such a problem in this area.

It may be worth asking a new question in an access group about the whole
issue of what exactly Access is locking that causes other stuff to fail
- I certainly don't have the experience with Access to have much of a
clue other than what I have already said.

Peter Jamieson

http://tips.pjmsn.me.uk

On 22/02/2010 13:45, Invi