Home  |  About  |   Search   

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

RunCommand Constants

Terms of Use


 

Bugs: Mailmerge starts new instance of Access

Author(s)
Dev Ashish

(Q)    When I run Mailmerge from Microsoft Word against an Access database, a new instance of Access is started.  How can I prevent this?

(A)    Word Mailmerge uses DDE to find a window with the title "Microsoft Access".  In Access 95 and 97, if you set an Application Title (under Tools/Startup), this title replaces the caption Microsoft Access and the DDE link is unable to find any instances of Access running.  Therefore, it (Word) starts a new instance of Access and tries to open the source database.

    The only proper workaround this is to

a - Remove the Application Title for the database.
b - Use ODBC to bypass Access and handle the tables directly for the data.

    However, in a large majority of cases, temporarily setting the database title back to "Microsoft Access" also works.  Try this function before changing the strategy to ODBC.

'************* Module Start *************
'
Option Compare Database
Option Explicit
Dim mstAppTitle As String

Function fSetAccessCaption() As Boolean
Dim dbs As Database
Const cPropNotExit = 3270

    'retrieve old title
    Set dbs = CurrentDb
    On Error Resume Next
    mstAppTitle = dbs.Properties("AppTitle")

    'if property doesn't exist
    If Err = cPropNotExit Then
        fSetAccessCaption = False
    Else
        dbs.Properties("AppTitle") = "Microsoft Access"
        RefreshTitleBar
        fSetAccessCaption = True
    End If
End Function

Sub sRestoreTitle()
    CurrentDb.Properties("AppTitle") = mstAppTitle
    RefreshTitleBar
End Sub

Function fMailMerge()
Dim objWord As Word.Document
Dim stMergeDoc As String

    If fSetAccessCaption Then
        On Error Resume Next
        stMergeDoc = "J:\install\Access mdbs\mailmerge.doc"

        Set objWord = GetObject(stMergeDoc, "Word.Document")

        objWord.Application.Visible = True

        objWord.MailMerge.OpenDataSource _
                Name:=CurrentDb.Name, _
                LinkToSource:=True, _
                Connection:="TABLE Customers", _
                SQLStatement:="Select * from [Customers]"
        objWord.MailMerge.Execute

        'restore the caption
        Call sRestoreTitle
    End If
End Function
'************* Module End *************

1998-2009, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer