Document Editing Problems

Connect Word Mail Merge to SQL Server Without an ODBC Data Source

Maira MehtabMaira Mehtab Nov 14, 2026 868 views

Question details

Connect a distributed Word mail merge document to a SQL Server view without requiring recipients to configure an ODBC data source.

Connect Word Mail Merge to SQL Server Without an ODBC Data Source
Product
Microsoft Word and SQL Server
Device & OS
not provided
Scenario
A macro-enabled mail merge document needs to reconnect when opened and produce labels using different data sources.
Observed behavior
Recipients should be able to open the document and reconnect to an authorized SQL Server view without setting up an ODBC data source.
Before you start

Confirm that recipients can reach SQL Server and have permission to query the relevant view. Check which non-ODBC provider, such as a SQL Server OLE DB provider, is available on their computers and whether their Word security settings permit trusted macros.

Solution 1Recommended

Reconnect the mail merge with Word VBA and an OLE DB provider

Use a macro-enabled document to store connection settings and reconnect to the SQL Server view when the document opens.

A document-open macro can read saved settings and call MailMerge.OpenDataSource to reattach the source. Using an OLE DB provider avoids an ODBC data source name, but recipients still need the appropriate provider, network access, and authorization.

The exact OpenDataSource arguments depend on the provider and Word environment. Test the connection on a recipient computer; automatic reconnection can still be interrupted by security prompts or authentication requirements.

1
Prepare the label document

In Word, use Mailings > Start Mail Merge > Labels to set up the label layout, then save the main document as a Word Macro-Enabled Document (.docm).

2
Store the connection settings

Open the VBA editor with Developer > Visual Basic. Add code that stores the provider connection settings, source name, SQL query, and mail merge document type in document variables. Do not store a password in the document.

3
Add the reconnection macro

In the document's ThisDocument module, add a Document_Open event that reads the variables and calls MailMerge.OpenDataSource with the appropriate connection and SQL query for the SQL Server view. Use an OLE DB provider rather than an ODBC connection.

4
Handle different sources

In the VBA editor, add a way to select or update the saved source and query before reconnecting. Keep the field names expected by the label layout consistent across the views.

5
Test the distributed document

Open the .docm file on a recipient computer, enable macros only if the document is trusted, and use Mailings > Preview Results to check that the expected label records appear.

Reconnect the mail merge with Word VBA and an OLE DB provider
Protect credentials: Use an appropriate secure authentication method, such as integrated authentication where available. Do not embed SQL Server passwords in document variables or VBA code.
Free Microsoft Office alternative

Edit mail merge documents with WPS Office

WPS Office is a free, lightweight Office alternative with a familiar interface and support for common Word formats, making it easy to move everyday documents between suites. The Word VBA and SQL Server reconnection workflow described above requires Microsoft Word and should be tested separately.

Work with Word documents in a familiar, easy-to-use interfaceOpen and edit common Microsoft Word formats, including DOC and DOCXPrepare and review document content for label and mail merge projectsMove everyday documents between WPS Office and Microsoft Office
microsoft office alternative - wps office

Frequently Asked Questions

Does this method eliminate the need for a database provider?

No. An OLE DB connection avoids configuring an ODBC data source, but recipients still need a compatible provider, network access, and permission to query the SQL Server view.

Can I store the SQL Server password in a document variable?

Avoid storing passwords in document variables or VBA code because recipients may be able to inspect them. Use a secure authentication method, such as integrated authentication where available.

Why doesn't the document reconnect when it opens?

Check whether macros are blocked, the required provider is installed, the SQL Server is reachable, and the recipient has access to the view. Also test the provider-specific MailMerge.OpenDataSource arguments in the VBA editor.

How can the same label layout use different SQL Server views?

Update the saved source and SQL query in the VBA workflow before calling MailMerge.OpenDataSource. The views should expose the field names used by the label document.

Will a Word add-in work without installing anything for recipients?

Not necessarily. An add-in that restores mail merge connections generally must be installed for each recipient, and its support for the chosen SQL Server provider and authentication method should be verified.