[InvisibleDuncan]

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...

[Doug Robbins - Word MVP]

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...

[InvisibleDuncan]

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

[Peter Jamieson]

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

[InvisibleDuncan]

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


.

[Peter Jamieson]

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


.

[Doug Robbins - Word MVP]

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