Problem with repeated access to the datasource (Excel Spreadsheet)

[Mike C.]

I have several Word documents that I use as main documents for email merges.
The merged data and email addresses come from two spreadsheets (not
simultaniously). I need to use the merge documents several times each day,
so I'd like to be able to create each one once and then re-use them from
there on out.

The problem is that now that I have each document created, they only work
right part of the time and I haven't been able to identify the cause of what
seems to be intermittant data errors. Sometimes, when I open one of the
merged documents, I get an error message saying "This operation cannot be
completed because of dialog or database engine failures. Please try again
later". I click OK. Then, I'm presented with a message saying "Word cannot
find its data source...", so I click "Find Data Source...", which works for
finding the data source, but then I have to re-define the recipient criteria,
which takes up even more time. I save the document, in hopes that it will
retain the data, which it does for a while, but after I open and use another
merge document, the first one stops working and I have to set it up again!
So annoying. Any ideas on how to fix this would really be appreciated.

Thanks!
Mike C.

 

[Peter Jamieson]

here could be more than one problem here, but do you have either of the
spreadsheets open twice at the same time - e.g. do you ever have two Mail
Merge Main documents, connected to the same Excel sheet, open at the same
time, or one Mail Merge main document and the sheet also open in Excel? If
so, you may find that only opening each sheet in one way at a time may help.

Also, which version of Office, and are you selecting a particular connection
method or using Word's default.

Peter Jamieson

 

[Mike C.]

Peter - Thanks for the troubleshooting. Good points to check. No, none of
those are the case, though. I'm in Office 2003 and am using the default
settings.

Also, I was wondering if this might go smoother if my data were in a single
location, specifically, in an Access table. I was thinking of doing some
sort of query in Access to combine the data from the spreadsheets into a
single table and then to use that for my email merge. Thoughts?

 

[Peter Jamieson]

I was thinking of doing some

sort of query in Access to combine the data from the spreadsheets into a
single table and then to use that for my email merge. Thoughts?

You can certainly get data into Access in a number of ways (link to an Excel
table, import data from an excel table, write a query that explicitly
queries the table) and I would certainly give it a try if the real source of
the problem does not become apparent.

Peter Jamieson

 

[Mike C.]

Thanks for the reply. Do you know of some good documentation to get me
started on this?

 

[Mike C.]

Thanks for the reply. Do you know of some good documentation to get me
started on this?

 

[Peter Jamieson]

Do you know of some good documentation to get me

started on this?

No, but...

(link to an Excel

To try either of these, open Access, create a new database, and use File|Get
External Data. There are two options - follow the prompts after that. You
can either import the data, or link to it. If you import the data, you will
need to do that manually before each merge, and there might be better and
simpler ways than that to manage the data (for example, maintaining it in
Access in the first place may be one possibility). If you link to the table,
you may need to relink later if, for example, the spreadsheet structure
changes, but try it and see. When you are asked for the data source, set the
"Files of type" to show "Microsoft Excel (*.xls)", then select your
workbook. (You might think you could also get the data via ODBC, but do not
bother to try ODBC Databases because it does not work for data sources such
as Excel).

Let's not go there yet - it uses the same underlying method as the above and
will probably suffer the same problems.

Peter Jamieson