Access 中 VBA 启动的邮件合并让 Word 再次打开数据库

Question

我正在开发一个 Access 数据库，该数据库通过从 Access 数据库中的 VBA 代码调用的邮件合并来生成一些邮件。问题是，如果我打开一个新的 Word 文档并启动邮件合并 (VBA)，Word 将打开相同的 Access 数据库（已打开）来获取数据。有什么办法可以防止这种情况发生吗？这样就可以使用已经打开的数据库实例了？

经过一些测试后，我得到了一个奇怪的行为：如果我打开按住 SHIFT 键的 Access 数据库，邮件合并不会打开同一数据库的其他 Access 实例。如果我在不按住该键的情况下打开 Access 数据库，我会得到所描述的行为。

我的邮件合并 VBA 代码：

On Error GoTo ErrorHandler

    Dim word As word.Application
    Dim Form As word.Document

    Set word = CreateObject("Word.Application")
    Set Form = word.Documents.Open("tpl.doc")

    With word
        word.Visible = True

        With .ActiveDocument.MailMerge
            .MainDocumentType = wdMailingLabels
            .OpenDataSource Name:= CurrentProject.FullName, ConfirmConversions:=False, _
                ReadOnly:=False, LinkToSource:=False, AddToRecentFiles:=False, _
                PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
                WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
                SQLStatement:="[MY QUERY]", _
                SQLStatement1:="", _
                SubType:=wdMergeSubTypeWord2000, OpenExclusive:=False
            .Destination = wdSendToNewDocument
            .Execute
            .MainDocumentType = wdNotAMergeDocument
        End With
    End With

    Form.Close False
    Set Form = Nothing

    Set word = Nothing

Exit_Error:
    Exit Sub
ErrorHandler:
    word.Quit (False)
    Set word = Nothing
    ' ...
End Sub

整个过程是使用 Access / Word 2003 完成的。

更新#1 如果有人能告诉我使用或不使用 SHIFT 键打开 Access 的确切区别是什么，也会有所帮助。如果可以编写一些 VBA 代码来启用“功能”，那么如果在没有 SHIFT 键的情况下打开数据库，它至少会“模拟”它。

干杯， 格雷戈尔

Answer 1

当我进行邮件合并时，我通常从 Access 导出 .txt 文件，然后将邮件合并数据源设置为该文件。这样，Access 只涉及导出查询，然后告诉 Word 文档通过自动化完成工作，大致如下：

    Public Function MailMergeLetters() 
           Dim pathMergeTemplate As String
            Dim sql As String
            Dim sqlWhere As String
            Dim sqlOrderBy As String


'Get the word template from the Letters folder  

            pathMergeTemplate = "C:\MyApp\Resources\Letters\"

'This is a sort of "base" query that holds all the mailmerge fields
'Ie, it defines what fields will be merged.

            sql = "SELECT * FROM MailMergeExportQry" 

            With Forms("MyContactsForm")

' Filter and order the records you want
'Very much to do for you

            sqlWhere = GetWhereClause()
            sqlOrderBy = GetOrderByClause()

            End With

' Build the sql string you will use with this mail merge

            sql = sql & sqlWhere & sqlOrderBy & ";"

'Create a temporary QueryDef to hold the query

                Dim qd As DAO.QueryDef
                Set qd = New DAO.QueryDef
                    qd.sql = sql
                    qd.Name = "mmexport"

                    CurrentDb.QueryDefs.Append qd

' Export the data using TransferText

                        DoCmd.TransferText _
                            acExportDelim, , _
                            "mmexport", _
                            pathMergeTemplate & "qryMailMerge.txt", _
                            True
' Clear up
                    CurrentDb.QueryDefs.Delete "mmexport"

                    qd.Close
                Set qd = Nothing

'------------------------------------------------------------------------------
'End Code Block:
'------------------------------------------------------------------------------
'------------------------------------------------------------------------------
'Start Code Block:
'OK. Access has built the .txt file.
'Now the Mail merge doc gets opened...
'------------------------------------------------------------------------------

                Dim appWord As Object
                Dim docWord As Object

                Set appWord = CreateObject("Word.Application")

                    appWord.Application.Visible = True

' Open the template in the Resources\Letters folder:

                    Set docWord = appWord.Documents.Add(Template:=pathMergeTemplate & "MergeLetters.dot")

'Now I can mail merge without involving currentproject of my Access app

                        docWord.MailMerge.OpenDataSource Name:=pathMergeTemplate & "qryMailMerge.txt", LinkToSource:=False

                    Set docWord = Nothing

                Set appWord = Nothing

'------------------------------------------------------------------------------
'End Code Block:
'------------------------------------------------------------------------------

        Finally:
            Exit Function

        Hell:
            MsgBox Err.Description & " " & Err.Number, vbExclamation, APPHELP

        On Error Resume Next
            CurrentDb.QueryDefs.Delete "mmexport"

            qd.Close
            Set qd = Nothing

            Set docWord = Nothing
            Set appWord = Nothing

            Resume Finally

        End Function

要使用此功能，您需要设置 Resources\Letters 子文件夹并将邮件合并模板 Word 文件放在那里。您还需要使用 Access 应用程序中的字段定义进行“基本”查询（在示例中，它称为 MailMergeExportQry。但您可以将其称为任何名称。

您还需要弄清楚将执行哪些过滤和排序。在示例中，这表示为

sqlWhere = GetWhereClause()
sqlOrderBy = GetOrderByClause

一旦你了解了这些事情，这是高度可重用的。