Link to home
Create AccountLog in
Avatar of Samantha Moore
Samantha MooreFlag 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


Avatar of Daniel Pineault
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
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account