Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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: Code:
objDoc.MailMerge.OpenDataSource Name:=CurrentDb.Name, OpenExclusive:=False, LinkToSource:=True, _ AddToRecentFiles:=False, Connection:="TABLE " & strTableName 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: Code:
objDoc.MailMerge.OpenDataSource Name:=CurrentDb.Name, OpenExclusive:=False, LinkToSource:=True, _ AddToRecentFiles:=False, SQLStatement:="Select * From " & strTableName, _ SubType:=wdMergeSubTypeWord2000 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... |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Access to Word mailmerge staggeringly slow
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: Code:
objDoc.MailMerge.OpenDataSource Name:=CurrentDb.Name, OpenExclusive:=False, LinkToSource:=True, _ AddToRecentFiles:=False, Connection:="TABLE " & strTableName 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: Code:
objDoc.MailMerge.OpenDataSource Name:=CurrentDb.Name, OpenExclusive:=False, LinkToSource:=True, _ AddToRecentFiles:=False, SQLStatement:="Select * From " & strTableName, _ SubType:=wdMergeSubTypeWord2000 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... |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Access to Word mailmerge staggeringly slow
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 |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Access to Word mailmerge staggeringly slow
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 |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Access to Word mailmerge staggeringly slow
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 |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Access to Word mailmerge staggeringly slow
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 . |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Access to Word mailmerge staggeringly slow
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 . |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Access to Word mailmerge staggeringly slow
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 |
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Access to Word mailmerge staggeringly slow
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 |
#10
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Access to Word mailmerge staggeringly slow
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 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mailmerge from Access to Word | Mailmerge | |||
MS Word 2003 slow with VPN access | Microsoft Word Help | |||
Access slow to large word document. | Microsoft Word Help | |||
word 2007 slow opening a mailmerge | Mailmerge | |||
Very Slow Directory Access | New Users |