Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    New Zealand
    Thanked 0 Times in 0 Posts

    Close Access After Mail Merge (Word & Access 97)

    I have a macro that runs an email mail merge using an Access query as a datasource. It opens new instances of Access each time it is run. Is there a way to close each instance of Access when it is finished?

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Silicon Valley, USA
    Thanked 94 Times in 90 Posts

    Re: Close Access After Mail Merge (Word & Access 97)

    What is the code you are using to open Access? For example:

    Set appAccess = CreateObject(ClassName)

    If so, you can probably do something like

    Set appAccess = Nothing

    Does this help?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Close Access After Mail Merge (Word & Access 97)

    Microsoft had a Knowledge Base article on this, but it's not available at the moment. It's a known problem.

    The standard method for getting data from Access is DDE. If the Access database has a title set in Tools/Startup..., DDE will start a new instance of Access.

    So one way to avoid multiple instances is to clear the database title.

    Another way is to use ODBC instead of DDE.

    If you specify the data source manually: make sure you check the box labeled Select Method in the "Open" dialog. Select the Access database and click Open. Select ... via ODBC and click OK.

    If you specify the data source in code, use something like

    With ActiveDocument.MailMerge
    .OpenDataSource _
    Name:="", _
    LinkToSource:=True, _
    ReadOnly:=True, _
    Connection:="DSN=MS Access database;DBQ=C:AccessDatabaseName.mdb", _
    SQLStatement:="SELECT * FROM qryQueryName"
    End With

    Note that the Name argument to OpenDataSource is empty.
    DSN= must be followed by the name of an ODBC data source for Access. Look it up in the ODBC Data Sources control panel. Create a new one if necessary.
    DBQ= must be followed by the path + name of the Access database.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts