Microsoft KB Archive/295264

= Mail Merge Wizard fails with stored procedures and functions =

Article ID: 295264

Article Last Modified on 6/8/2004

-

APPLIES TO


 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition
 * Microsoft Word 2002 Standard Edition

-



This article was previously published under Q295264



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access project (.adp).



SYMPTOMS
When you run the Mail Merge Wizard to merge the results of a SQL Server stored procedure or function with a Microsoft Word document, you receive the following error message in Microsoft Word:

is locked for editing by 'another user'.

When you click Cancel, you receive the following error message in Microsoft Access:

Word was unable to open the data source.

When you click OK two times, the merge proceeds, but you cannot read the data; that is, it looks encrypted.



CAUSE
The Mail Merge Wizard uses incorrect SQL syntax to retrieve data from stored procedures and inline functions. The wizard uses an SQL statement in the form of &quot;SELECT * FROM ObjectName&quot;. This syntax fails when trying to execute an inline function or stored procedure.



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:

 Open your project (.adp) file in Microsoft Access. In the Database window, click Queries under Objects, and then click New. In the New Query dialog box, click Design View, and then click OK. In the Add Table dialog box, click the Functions tab. Select your inline function, click Add, and then click Close. In the field list, click to select the check box next to * (All Columns).</li>  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 </li> On the File menu, click Save. Type a name for the new view, and then click OK.</li> Close the view.</li> Use the Mail Merge Wizard to merge the newly created view with Microsoft Word.</li></ol>

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 &quot;Merging the Results of an Inline Function&quot; 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: <ol> Open a Microsoft Access database (.mdb) file.</li> In the Database window, click Queries under Objects, and then click New.</li> In the New Query dialog box, click Design View, and then click OK.</li> In the Add Table dialog box, click Close without adding any tables or queries.</li> On the Query menu, point to SQL Specific, and then click Pass-Through.</li>  Enter the appropriate SQL syntax for executing your stored procedure, for example: EXEC MyStoredProcedure </li> On the View menu, click Properties.</li> In the ODBC Connect Str property, click the Build button.</li> 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.</li> Set the Returns Records property to Yes.</li> On the Query menu, click Run. The stored procedure's resultset should be displayed in Datasheet view.</li> <li>On the File menu, click Save. Type a name for the new pass-through query, and then click OK.</li> <li>Close the query.</li> <li>Use the Mail Merge Wizard to merge the newly created pass-through query with Microsoft Word.</li></ol>

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in Microsoft Access.

<div class="moreinformation_section">

Steps to Reproduce the Behavior
<ol> <li>Open the sample project NorthwindCS.adp.</li> <li>Close the Main Switchboard form if it appears.</li> <li>On the View menu, point to Database Objects, and the click Queries.</li> <li>Click the Ten Most Expensive Products stored procedure in the Database window.</li> <li>On the Tools menu, point to Office Links, and then click Merge it with Microsoft Word.</li> <li>In the Mail Merge Wizard dialog box, click the option to create a new document, and then click OK.

Note that Microsoft Word starts, and you receive the following error message:

NorthwindCS.adp is locked for editing by 'another user'.

</li> <li>Click Cancel.</li> <li>Switch back to Microsoft Access.</li></ol>

Note that you receive the following error message:

Word was unable to open the data source.

Additional query words: pra ACC2002

Keywords: kberrmsg kbbug kbnofix kbclientserver KB295264

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.