Avatar of Samantha Moore
Samantha Moore
Flag for United States of America asked on

MSAccess driven mail merge with word doesn't work on Server 2019 with Office 2016

In the middle of a port of an MSAccess application from a server 2006 using MSAccess 2010 with Jet databases, to a server 2019 running Office 2016 with an SQLServer 2019.  When my access application attempts to run word mailmerge to print some stuff, then I get the message:  

Error 4605 - The OpenDataSource method or property is not available because this command is not available for reading. - in MailMergePatientDocument()

On the original environment it works like a charm.  On the ported environment it always gives me that error.  If I pick the same document and run a mail merge from within word, there is no problem connecting to the new SQLServer database.  It is when I have the access application fire up word and do the job that it doesn't work.  The process fails on the .ActiveDocument.Mailmerge.Execute instruction .

Any insights would help.


The code that does this:
Private Sub MailMergePatientDocument(InputFilename As String, Optional PatientID As Long = 0, Optional DoubleSided As Boolean = False)
    Dim QryPatient As String
    Dim DatabaseType As String
    Dim objapp As Object
   
    QryPatient = "SELECT * from [tblPatientInfo]"
    If PatientID <> 0 Then
        QryPatient = QryPatient & " WHERE [PatientID]=" & PatientID
    End If
   
    On Error GoTo error
    Set objapp = CreateObject("Word.Application")
    With objapp
        .Visible = True 'Make it visible
        .Documents.Open InputFilename, False, True 'Open the Mailmerge Document
        .ActiveDocument.Mailmerge.MainDocumentType = wdFormLetters
        .ActiveDocument.Mailmerge.Destination = 0 'wdSendToNewDocument
        '.ActiveDocument.MailMerge.Destination = 1 'wdSendToPrinter
       
        'Depending on how the database is configured:
        DatabaseType = GetSystemParm("DATABASETYPE")
         'Attach the data source to the mail merge document
        If DatabaseType = "Jet" Then
            .ActiveDocument.Mailmerge.OpenDataSource Name:="D:\Bear\BearInterface.mdb", _
                SqlStatement:=QryPatient
        Else
            .ActiveDocument.Mailmerge.OpenDataSource Name:="", _
                Connection:="DSN=BearDatabase;DATABASE=BearDatabase;", _
                SqlStatement:=QryPatient
        End If

'      Execute the merge
        .ActiveDocument.Mailmerge.Execute pause:=True
        If DoubleSided Then
            .ActiveDocument.PrintOut ManualDuplexPrint:=True, background:=False
        Else
            .ActiveDocument.PrintOut ManualDuplexPrint:=False, background:=False
        End If
        .ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
        .Quit SaveChanges:=wdDoNotSaveChanges 'close all documents

    End With
    Exit Sub
   
error:
    MsgBox "Error: " & Err.Number & " - " & Err.Description & " - in MailMergePatientDocument()"
    On Error Resume Next
    objapp.Quit SaveChanges:=wdDoNotSaveChanges
    On Error GoTo 0
    Exit Sub
End Sub


Microsoft OfficeMicrosoft AccessMicrosoft Word

Avatar of undefined
Last Comment
Jim Dettman (EE MVE)

8/22/2022 - Mon
Daniel Pineault

When it comes to Mail Merging from Access I always point people to Albert Kallal's Super Easy Word Merge found at http://kallal.ca/msaccess/msaccess.html I used a disconnected approach via an intermediary text file.  I high suggest you look it over.
ASKER CERTIFIED SOLUTION
Jim Dettman (EE MVE)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Your help has saved me hundreds of hours of internet surfing.
fblack61