Fix Missing Records in Word Mail Merge Connected to Access Query
Question details
The user needs to resolve an issue where a Microsoft Word mail merge fails to display all recipient records when connected to a complex Microsoft Access query.
- Product
- Microsoft Word, Microsoft Access
- Device & OS
- not provided
- Scenario
- Executing a mail merge in Word using a complex Access query (containing multiple OR conditions, IIf expressions, and joins) as the data source.
- Observed behavior
- The recipient list in Word shows fewer records than the actual query results in Access, often ignoring criteria in the second half of complex OR statements.
Ensure you have permission to edit queries in your Access database and verify that the original select query returns the correct number of records when run directly in Access.
Use a Make Table Query to Materialize the Data
Convert the complex query into a Make Table query to create a temporary table. This provides Word with a static, materialized data set instead of forcing it to re-evaluate complex SQL statements.
Microsoft Word uses OLE DB to communicate with Access during a mail merge. When connecting directly to a complex query containing SELECT DISTINCT, nested IIf statements, and multiple OR conditions, the OLE DB provider often fails to evaluate all criteria correctly, resulting in dropped records.
Open your database in Microsoft Access. Open your complex query in Design View, navigate to the Query Design ribbon, and click 'Make Table'. Enter a name for the new temporary table (e.g., tblMergeData).
Click 'Run' on the ribbon to execute the Make Table query. This action evaluates all complex SQL statements internally within Access and writes the finalized data into the temporary table.
Open your Microsoft Word document. Go to Mailings > Select Recipients > Use an Existing List. Connect to your Access database and select the newly created temporary table instead of the query.
To streamline this workflow, add a short VBA script or a Macro step in Access that automatically runs the Make Table query right before the Word mail merge is initiated.
Export Access Query Results to Excel
Export the correctly evaluated Access query results to an Excel spreadsheet, and use that file as the data source for your Word mail merge.
Experience Flawless Mail Merges with WPS Office
If troubleshooting complex Microsoft Access and Word integration issues is slowing you down, consider switching to WPS Office. With robust compatibility for .docx and .xlsx formats, WPS Office allows you to seamlessly manage your recipient data in WPS Spreadsheet and execute error-free mail merges in WPS Writer—without the need for complex SQL workarounds.
- 1. Download and Install: Visit the official WPS website to download the free suite and install it in minutes.
- 2. Prepare Data in Spreadsheets: Easily organize your recipient list in WPS Spreadsheet, sidestepping the complexities of database queries.
- 3. Execute the Mail Merge: Open WPS Writer, navigate to the Mail Merge tab, select your spreadsheet, and generate your documents flawlessly.
Frequently Asked Questions
Why does Word drop records when connected to a complex Access query?
Word connects to Access databases using OLE DB. This connection protocol often struggles to properly evaluate complex SQL syntax—such as nested IIf functions, SELECT DISTINCT, and multiple OR conditions—causing it to silently drop records that fail to meet partially evaluated criteria.
Does converting to a Make Table query permanently change my Access database?
It adds a new temporary table to your database and changes your query's action to overwrite this table each time it is run. The original source tables and your raw data remain intact and unaffected.
Can I automatically refresh the Make Table query from Word?
Not directly from Word's native interface. You will need to either manually run the query in Access before opening the Word document, or write a VBA script that triggers the Make Table query execution automatically.
