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/Word2003完成的。

Update #1 It would also help if someone could tell me what the exact difference is between opening Access with or without the SHIFT-Key. And if it is possible to write some VBA code to enable the "features" so if the database is opened without the SHIFT-Key, it at least "simulates" it.

Cheers, Gregor

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:MyAppResourcesLetters"

 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 ResourcesLetters 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

要使用它，您需要设置ResourcesLetters子文件夹，并将您的mailmerge模板word文件放入其中。您还需要在Access应用程序中使用字段定义的“基本”查询（在本例中，它被称为MailMergeExportQry。但您可以将其称为任何名称。

你还需要弄清楚你将要做什么过滤和排序

sqlWhere = GetWhereClause()
sqlOrderBy = GetOrderByClause

一旦你了解了这些东西，这是高度可重复使用的。

友情链接