Mail Merge launched by VBA in Access allows Word to open the database again

Question

I am working on an Access database that generates some emails using a merge called from VBA code in an Access database. The problem is that if I open a new Word document and start merging (VBA), Word will open the same Access database (which is already open) to receive the data. Is there any way to prevent this? So, is the database instance already open?

After some testing, I get a strange behavior: if I open an Access database containing a SHIFT key, then a merge will not open another Access instance of the same database. If I open the Access database without saving the key, I will get the described behavior.

My email merge code is:

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 

It's all about Access / Word 2003.

Update # 1 It would also help if someone could tell me what exactly is the difference between opening access with or without a SHIFT key. And if you can write some VBA code to enable "functions", therefore, if the database is opened without a SHIFT key, it at least "mimics" it.

Cheers, Gregor

Answer 1

When I do mailmerges, I usually export the .txt file from Access and then set the merge data source for it. Thus, Access is only involved in exporting the request, and then informs the Word document of the work through automation, approximately as follows:

  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 

To use this, you need to configure your "Resources \ Letters" folder and place the mailmerge template word file in it. You also need your “basic” query with field definitions in your Access application (in this example, it is called MailMergeExportQry, but you can call it anything.

You also need to figure out what kind of filtering and sorting you will do. In the example, this is represented

 sqlWhere = GetWhereClause() sqlOrderBy = GetOrderByClause 

Once you are deceived by these things, it is very reused.