select recipients list too big and slow

[Michael Donnary]

I am using Word 2002 and SQL Server 2000. The data source
contains over 10,000 records and takes a long time to open
and work in the "Select Recipients List". I would like to
open the data source and prompt the user to enter a single
record in a form. This data would then be passed in to
the "where" text string in the query. For example: The
user would be prompted to enter a customer number. The
record set returned would contain only one record and
would be much faster to work with. Any ideas? Thanks.

 

[Peter Jamieson]

How about setting up the mail merge main document with a query option that
selects a small number of records in the data source (i.e. at least one that
you know will always be there) and showing the user(s) how to get to and
modify the query options. That way you may be able to improve performance
but still allow the user some flexibility in querying (if that is desirable)
without any needfor VBA code which you will otherwise almost certainly need.
I don't know for sure it would improve matters but could be worth a try.

 

[Michael Donnary]

That would be great. Can you tell me how I would set up the initial query?

 

[Peter Jamieson]

OK, this obviously isn't going to work if you can't load the entire set of
records in the first instance.

But assuming you can connect to the data source, do that, then go to the
Recipients dialog box then
a. click on the down arrow in the column you want to use for your selection
criterion and either
b. select a value that you know will continue to exist on the table and
which will reduce the number of records or
c. click the Advanced... option and enter the filter criterion in the
"Filter and Sort" grid

Then save and close the document, re-open it and verify that the connection
is faster and only the expected reocrds are there.

The users will need to use option (c), but with any luck all they will need
to do is modify the ID in the "Comoare to" column in "Filter and Sort", and
try to avoid selecting "All" in the drop-down by mistake.

 

[Michael Donnary]

I gave it a try and now have a new problem. When I reopen the document, I
receive an error message that the data source cannot be found. If I search
for it and open it, the recipient list is populated will all values again.

I have searched the newsgroup for the problem and did not find a solution.

By the way, If I reopen the recipient list without closing and reopening the
document the performance is great!

Thanks for your help.

 

[Peter Jamieson]

I gave it a try and now have a new problem. When I reopen the document, I

receive an error message that the data source cannot be found.

Aargh! This problem has come up before, but only in some circumstances. A
couple of things to check
a. is Windows Explorer set up to display file extensions? If not, please
change the value in Explorer Tools|Folder Options|View and try again. But if
your connection saved successfully before, I doubt if that is the problem
b. Does the name of the column you are using for the filter contain any
spaces? If so, you will either need to create a View in SQL Server or
connect some other way (ODBC+MS Query probably) in which case this route is
probably not going to save you any time and a bit of VBA may be needed
anyway.

If neither of those things is applicable, the simplest way to set up the
merge data source using VBA would probably be as follows:
a. create an AutoOpen subroutine in the document (see http://word.mvps.org
for further info on this, including other ways to get macros to run
automatically. The sub should contain at least

Sub AutoOpen()

' Needs error management...

With ActiveDocument.MailMerge
' Substitute the main document type you need
.MainDocumentType = wdFormLetters
.OpenDataSource _
Name:="the full path name of the .odc file you set up", _
Connection:="can be blank I think, or contain any additional connection
info you need", _
SQLStatement1:="the SQL query you need"

' Substitute the default destination you want (the users will override
this anyway)
.Destination = wdSendToNewDocument
End With

End Sub

b. test the macro
c. make the document a "normal" Word document (which removes the data
source. You can do this using the first button in the mail merge toolbar or
using VBA:

ActiveDocument.Mailmerge.MainDocumentType = wdNotAMergeDocument

d. save and close the document.
e. open the document and verify that you see the records you need.

If that works then it would not be a large step to add code to prompt for a
Customer ID.

If you still lose the connection on open then I would guess some security
info is missing.

You may be able to discover the query syntax required by using

print ActiveDocument.Mailmerge.DataSource.QueryString

in the Visual Basic immediate window with your existing, working document
open.

If Word does not let you do that, save the document as a "Web Page", open it
as a text file so you can see the HTML, and you will find the connection
info. and query info in a block near the top of the file.