Prompt to Select Table with Word 2002 Mail Merge Code for Excel or Access Data Source (289830)

CAUSE

In Word 97 and Word 2000, if you use the path/file name for an Excel workbook or an Access database for the Name argument in the OpenDataSource method, Word uses Dynamic Data Exchange (DDE) to access the data for your mail merge, as follows:

'Retrieves data from a table in an Access database.
Dim oMM as Word.MailMerge
Set oMM = ActiveDocument.MailMerge
oMM.MainDocumentType = wdFormLetters
oMM.OpenDataSource Name:= "c:\db1.mdb", Connection:= "TABLE Customers"

-or-

'Retrieves data from a named range in an Excel workbook.
Dim oMM as Word.MailMerge
Set oMM = ActiveDocument.MailMerge
oMM.MainDocumentType = wdFormLetters
oMM.OpenDataSource Name:= "c:\book1.xls", Connection:= "MyNamedRange"

When this code is used with Word 2002, Word automatically uses the Office DataSource Object (ODSO) to access the data with OLEDB and not DDE. ODSO expects an SQL query for the data source in the SQLStatement argument of OpenDataSource and ignores the Connection argument. Because the SQLStatement argument is omitted in the code above, ODSO displays the Select Table dialog box.

RESOLUTION

To avoid the prompt, you can do the following:

Supply the SQLStatement argument of the OpenDataSource method with a valid SQL query. Word 2002 uses OLEDB (through ODSO) for the data access, whereas Word 97 and Word 2000 continue to use DDE. -or-
Force Word 2002 to use DDE instead of OLEDB for the data access by supplying the SubType argument of the OpenDataSource method with wdMergeSubTypeWord2000. Note that the SubType argument is new to Word 2002; Word 97 and Word 2000 do not support this argument.

For additional information on the different data access methods you can use for a Word 2002 mail merge, click the article number below to view the article in the Microsoft Knowledge Base:

285332 HOWTO: Automate Word 2002 with Visual Basic to Create a Mail Merge

MORE INFORMATION

Steps to Reproduce Behavior

In Word 2002, create a new document.
Press ALT+F11 to open the Microsoft Visual Basic for Applications (VBA) Editor.
On the Insert menu, click Module to add a new module to the VBA project.

Add the following code to the new code module and modify the path to the sample Access Northwind.mdb database in the code to match your installation path for Office XP.

Sub DoMerge()
    Dim oMM As Word.MailMerge
    Set oMM = Documents.Add.MailMerge
    oMM.MainDocumentType = wdFormLetters
    oMM.OpenDataSource _
        Name:="c:\program files\microsoft office\office10\samples\northwind.mdb", _
        Connection:="TABLE Customers"
    oMM.EditMainDocument
    oMM.Fields.Add Range:=Selection.Range, Name:="CompanyName"
    oMM.Destination = wdSendToNewDocument
    oMM.Execute
    ActiveWindow.View.Type = wdNormalView
End Sub

Close the VBA Editor to return to the document in Word.
On the Tools menu, point to Macro and then click Macros. In the macro list, select DoMerge and then click Run.

Word 2002 Behavior: The Select Table dialog box appears. In the list, select the Customers table and then click OK to complete the mail merge.

Word 97/2000 Behavior: No dialog box appears, and the mail merge completes without user intervention.

To avoid the prompt to select a table in Word 2002, modify the call to OpenDataSource in the code to either of the following:

'Use OLEDB through ODSO to access the data.
oMM.OpenDataSource _
     Name:="c:\program files\microsoft office\office10\samples\northwind.mdb", _
     SQLStatement:="Select * From [Customers]"