Mail merge started by VBA in Access let Word open Database again

Question

I'm working on a Access database which generates some mails with mail merge called from VBA code in the Access database. The problem is that if I open a new Word document and start the mail merge (VBA), Word opens the same Access database (which is already open) to get the data. Is there any way to prevent this? So that the already opened instance of the database is used?

After some testing I get a strange behavior: If I open the Access database holding the SHIFT-Key the mail merge does not open an other Access instance of the same database. If I open the Access database without holding the key, I get the described behavior.

My mail merge VBA code:

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

The whole thing is done with Access / Word 2003.

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

Kraal

opening Access while holding Shift key down prevents autoexec macro and other automatic startup things to happen. Note that you can disable this possibility. Google for "AllowBypassKey". – Denim

I do not wanna disable it, I more like need it... It is really strange: Holding the Shift key and everything works just fine. Not holding it and Word will open each time a new Access instance and from there the Access database... – Kraal

You have an .OpenDataSource command but no .Close, is that ok ? (I am not familiar with the Word vba). Otherwise, I don't think it's a problem if the db is opened a second time, as long as you close it afterwards. – Denim

It looks like it is ok. There isn't any Close method. The problem with add some code to the Word templates is, that it could also close the Access instance which the user opened himself. So there is, as far as I see it, no way to determine when to close / not close the datasource. Also, I do not see any event on which I could hook such a close method in Word – Kraal

This is why it is usually easier to output a text file and to use that for your Access mailmerge. I often use a simplified version of: tek-tips.com/faqs.cfm?fid=5088 – Wilkie

@Remou: Thank you for your link. I see more and more that it was a big mistake to ignore the advice to use plain text files :-(. I've a look at this and try to implement it in my Access DB. At least I get paid for the pain :-P – Kraal

Heh :) Well, get back here if you need any help. It is really quite easy to use a text file, you do not need to go to the lengths in the link. – Wilkie

It already works quite well. I think this is a nice way to go around those problems – Kraal

The SHIFT KEY bypass would be irrelevant if you were using ODBC instead of DDE to do the mail merge. However, that also means that if your data source uses VBA functions or UDFs, it won't work. In that case, it's usually easier to write out the data to some kind of temp location, either a temp table in Access or an external text file. The key is to avoid DDE, which opens a full instance of Access in order to get to the data. – Overstreet

Answer 1

When I do mailmerges, I usually export a .txt file from Access and then set the mail merge datasource to that. That way Access is only involved in exporting the query and then telling the Word document to do the work via automation, roughly 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 set up your Resources\Letters subfolder and put your mailmerge template word file in there. You also need your "base" query with the field definitions in your Access App (in the example, it is called MailMergeExportQry. But you can call it anything.

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

sqlWhere = GetWhereClause()
sqlOrderBy = GetOrderByClause

Once you have got your head round those things, this is highly reusable.

Trainer

It's like Remou's comment and it did the job. Thank you. As you weren't the first one with this idea here, I just upvote it. Thank you! – Kraal

On the other hand, it is a working solution that others can use (that took a lot of formatting in SO...). By accepting it you make Stackoverflow better. Ie, a place where you can easily find answers to questions. 5 points are neither here nor there but making stackoverflow better isn't. – Trainer