Mail Merge Wizard fails with stored procedures and functions (295264)

RESOLUTION

Merging the Results of an Inline Function

To merge the results of an inline function, create a view that selects the fields that you want from the inline function, and then merge the view with Microsoft Word. To do so, follow these steps:

1. Open your project (.adp) file in Microsoft Access.
2. In the Database window, click Queries under Objects, and then click New.
3. In the New Query dialog box, click Design View, and then click OK.
4. In the Add Table dialog box, click the Functions tab.
5. Select your inline function, click Add, and then click Close.
6. In the field list, click to select the check box next to * (All Columns).
7. On the View menu, point to Show Panes, and then click SQL. The SQL pane should contain a statement similar to the following:

   SELECT MyFunction.* FROM MyFunction() MyFunction
   					

8. On the File menu, click Save. Type a name for the new view, and then click OK.
9. Close the view.
10. Use the Mail Merge Wizard to merge the newly created view with Microsoft Word.

Merging the Results of a Stored Procedure

Unfortunately, there is no ideal approach for merging the results of a stored procedure with Microsoft Word. There are several approaches you can take, but each has its own advantages and disadvantages.

Rewrite the Stored Procedure as an Inline Function

The first approach is to rewrite the stored procedure as an inline function, and then to use the steps in the "Merging the Results of an Inline Function" section earlier in this article to create a view based on the function to merge with Microsoft Word.

Use a Make-Table Query to Select the Results of the Stored Procedure into a New Table

The second approach is to modify the stored procedure so that its resultset is copied into a new table. For example, if the stored procedure's current syntax is similar to:

SELECT Customers.* FROM Customers

then you could change it to:

SELECT Customers.* INTO tblCustomers FROM Customers

After running the stored procedure, you could merge the newly created table with Microsoft Word.

Execute the Stored Procedure Using an ODBC Pass-Through Query

The third approach is to execute the stored procedure from an ODBC pass-through query in a Microsoft Access database. You can then merge the ODBC pass-through query with Microsoft Word. To do so, follow these steps:

1. Open a Microsoft Access database (.mdb) file.
2. In the Database window, click Queries under Objects, and then click New.
3. In the New Query dialog box, click Design View, and then click OK.
4. In the Add Table dialog box, click Close without adding any tables or queries.
5. On the Query menu, point to SQL Specific, and then click Pass-Through.
6. Enter the appropriate SQL syntax for executing your stored procedure, for example:

   EXEC MyStoredProcedure

7. On the View menu, click Properties.
8. In the ODBC Connect Str property, click the Build button.
9. Select an ODBC data source, and then log on to the SQL Server database that contains the stored procedure. When you are prompted, you can optionally choose whether to include the password in the connection string.
10. Set the Returns Records property to Yes.
11. On the Query menu, click Run. The stored procedure's resultset should be displayed in Datasheet view.
12. On the File menu, click Save. Type a name for the new pass-through query, and then click OK.
13. Close the query.
14. Use the Mail Merge Wizard to merge the newly created pass-through query with Microsoft Word.