Mail Merge with MS Access data source (Word/Access 2K)

Topic

New Reply

I have a database in Access where the user wants to do an envelope mail merge in Word. I’ve figured out enough to make the Word document to include envelopes and to initially pull data from a query I have in the database, and created an ODC file for the data access (if I understood it correctly). In testing it I found that if I change the values in the database those changes aren’t reflected in the Word document. My goal is to provide the end user with an already merged Word document with the data dynamically updated from the query I created in the database. I will also need to know how to alter the data source so I can tell the user how to point it to the database in whatever directory they decided to put it in on their hard drive.

Reply | Quote

Replies

“Already merged” and “dynamically updated” seem to be two things that do not mix.

As an alternative, and depending on the nature of the data, you could give the user a button to query the database and update data elements stored as document variables. To display those data elements into the document, you would pre-position DOCVARIABLE fields, populated with default or start values. Then at the end of the macro that updates those values you would refresh the fields in the document to display the changed variables.

Reply | Quote

Mail merge will not allow you to update the merged document without doing the merge over. So one alternative is to do a merge every time the datasource changes.

Another is to use Insert/Database button from the Database toolbar. This will automatically update when the source file changes. However, if you want a particular format, it’s probably easier to do a query in Access & then link to the query.

Reply | Quote

Phil, If I understand you correctly, a link to a query is exactly what I’m looking for. But when you open the Word document, it would need to update itself using the query in the MS Access database. It would also be helpful if there was a button in Word to refresh this dataset and update the merge Word document (maybe this is what you already described). Is it possible to create the merge in Word, tie it to a query in an Access database, and give it to the end user with instruction on how to change the Word merged datasource to point to the directory they put the Access DB in?

Reply | Quote

Hi Mike:
Sorry for the delay, but I’ve been offline all day. If you insert the database, this is a field that will update. However, it will look like a table from Access. Yes, you can use queries. If you show the Database toolbar in Word, you’ll see a button for inserting a database. Browse for it &, if there are queries in the database, you will get a dialog box letting you select which query you want. You will have a choice between DDE & ODBC. I would try using DDE.

Once you insert the database, there is another button on the Database toolbar to update the field. Also, if you go to Insert/Field…/Database/Options, you will see a number of switches that you can use to choose which records to use & numerous other options.

However, the structure of the end document is not as flexible as when doing a mail merge. There, you can place merge fields anywhere in the document. If I knew what type of structure you wanted your end document to have, I might be able to be more specific.

P.S. Just for clarity, when talking about a mail merge, Word uses 3 terms: the datasource (your database), the main document (contains the structure & instructions for the merge), & the merged document (the result).
Hope this helps,

Reply | Quote

Hi Mike:
Sorry for the delay, but I’ve been offline all day. If you insert the database, this is a field that will update. However, it will look like a table from Access. Yes, you can use queries. If you show the Database toolbar in Word, you’ll see a button for inserting a database. Browse for it &, if there are queries in the database, you will get a dialog box letting you select which query you want. You will have a choice between DDE & ODBC. I would try using DDE.

Once you insert the database, there is another button on the Database toolbar to update the field. Also, if you go to Insert/Field…/Database/Options, you will see a number of switches that you can use to choose which records to use & numerous other options.

However, the structure of the end document is not as flexible as when doing a mail merge. There, you can place merge fields anywhere in the document. If I knew what type of structure you wanted your end document to have, I might be able to be more specific.

P.S. Just for clarity, when talking about a mail merge, Word uses 3 terms: the datasource (your database), the main document (contains the structure & instructions for the merge), & the merged document (the result).
Hope this helps,

Reply | Quote

Phil, If I understand you correctly, a link to a query is exactly what I’m looking for. But when you open the Word document, it would need to update itself using the query in the MS Access database. It would also be helpful if there was a button in Word to refresh this dataset and update the merge Word document (maybe this is what you already described). Is it possible to create the merge in Word, tie it to a query in an Access database, and give it to the end user with instruction on how to change the Word merged datasource to point to the directory they put the Access DB in?

Reply | Quote

Mail merge will not allow you to update the merged document without doing the merge over. So one alternative is to do a merge every time the datasource changes.

Another is to use Insert/Database button from the Database toolbar. This will automatically update when the source file changes. However, if you want a particular format, it’s probably easier to do a query in Access & then link to the query.

Reply | Quote