Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    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?
    Thanks

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 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

    appAccess.Quit
    Set appAccess = Nothing

    Does this help?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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"
    .Execute
    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
  •