Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Strange error trying to do mail merge (Access 2003

    I have been supporting a membership tracking DB app since Access 97 days. Back when I was using Access 2K I added functionality to do a mail merge through code generating reminder letters for folks that haven't renewed their membership. It uses a Word template and the mail merge functionality as in the code snippet below:

    ' Start up Word.
    Set wrdApp = New Word.Application
    Set doc = wrdApp.Documents.Add(strPath & CSTRTEMPLATE)

    001 ' Set up and perform the mail merge.
    002 DoCmd.SetWarnings False
    003 With doc.MailMerge
    004 .OpenDataSource Name:=CurrentDb.Name, _
    LinkToSource:=True, _
    AddToRecentFiles:=False, _
    Connection:="QUERY " & CSTRQUERY
    008 .Destination = wdSendToNewDocument
    009 .SuppressBlankLines = False
    010 With .DataSource
    011 .FirstRecord = wdDefaultFirstRecord
    012 .LastRecord = wdDefaultLastRecord
    013 End With
    014 .Execute
    015 End With
    016 wrdApp.Visible = True

    This worked without a problem in A2K and Word 2K, However, now that we have upgraded to Office 2003, at line 004 it displays a dialog entitled "Datalink Properties". Near the bottom of the dialog is a "Test Connection" button. If I click it, it almost immediately returns an error "Test connection failed because of an error in initializing provider. Could not use "; file already in use.

    If I click OK, a new document based on the template specified is displayed and the "Confirm Data Source" dialog displays with "OLE DB Database Files" selected. I clicked OK again and it returns me to the Datalink Properties dialog. I clicked OK on this dialog again and I get "Error 5922: Word was unable to open the data source".

    I did a search in the Microsoft KB and MSDN for the error that I get when I try to test the connection, but came up dry.

    What has changed in the Office Automation object model that would cause code that worked in A2K to fail in Access 2003? Any thoughts on how I can get this to work again?

    I must admit that I've been away from Access for quite a while now as my work has focused on porting Access DBs to a Linux platform.

    Thanks for any help that can be provided.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Strange error trying to do mail merge (Access

    Yes Mail Merge changed with 2003.

    I add one extra line into the With doc.MailMerge section

    SubType:=wdMergeSubTypeWord2000

    This just means do it the old way.
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Strange error trying to do mail merge (Access 2003

    More precisely, mail merge changed in Word 2002, and SubType is an argument to OpenDataSource:

    .OpenDataSource Name:=CurrentDb.Name, _
    LinkToSource:=True, _
    AddToRecentFiles:=False, _
    Connection:="QUERY " & CSTRQUERY, _
    SubType:=wdMergeSubTypeWord2000

    You need to specify the "old" subtype if you use DDE (as in your example) or ODBC to connect to the database. The default method is now OLE DB.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange error trying to do mail merge (Access

    Thank you. Now, I guess the next question is what is the "new way" to do it? I don't expect you to tell me the whole story, but where can I go to learn about the new approach? It just seems that if I need to modify the code anyway, I may as well do it right instead of adding something that may break this functionality later when Microsoft decides not to support the "old way" anymore.

    Thanks again for the help.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Strange error trying to do mail merge (Access

    I don't think you need to worry about that. There's another point to consider: of the three available methods of connecting to a database (OLE DB, DDE and ODBC), DDE is the only one in which you can use parameter queries and queries that refer to controls on a form. OLE DB and ODBC operate on a lower level, so they don't "know" about parameters and forms.
    If you still want to try OLE DB, it's probably best to record a macro and edit the result to use CurrentDb.Name instead of a hard-coded path. For example:

    ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, _
    Connection:= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentDb.Name, _
    SQLStatement:="SELECT * FROM `" & CSTRQUERY & "`"

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange error trying to do mail merge (Access

    Ok, I understand. I added the parameter you recommended to the OpenDataSource statement and everything worked great. I guess I'll just leave well enough alone.

    Thanks again for the help.

Posting Permissions

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