Unexpected Big Problem With Mail Merge via Excel VBA

JennEx · #1 05-24-2019, 05:40 AM

I have this code in an Excel VBA project.

Code:

 Set objWord = CreateObject("Word.Application")
    With objWord
        .DisplayAlerts = False
        .Visible = True
        Set oDoc = .Documents.Open(Filename:=fName, ConfirmConversions:=False, _
            ReadOnly:=True, AddToRecentFiles:=False, Visible:=True)
        With oDoc
            With .MailMerge
                .MainDocumentType = wdFormLetters
                .Destination = wdSendtToNewDocument
                .SuppressBlankLines = True
                .OpenDataSource Name:=StrSrc, AddToRecentFiles:=False, LinkToSource:=False, ConfirmConversions:=False, _
                    ReadOnly:=True, format:=wdOpenFormatAuto, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "User ID=Admin;Data Source=" & StrSrc & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";", _
                    SQLStatement:=StrSQL, SQLStatement1:="", SubType:=wdMergeSubTypeAccess
                .Execute Pause:=False
            End With
            .Close False
        End With

I use this code to facilitate the mail merge feature of Word from an event in my Excel VBA project. I am getting an error with the line in red.
"The OpenDataSource method or property is not available because this command is not available for reading."

A few interesting observations (to me anyway) that may help diagnose the problem ...
This code was written last year on my work computer and worked flawlessly. I opted to clean up some of my mail merge documents (formatting etc) from home last night and in my testing there did not get that error, and things worked as expected. This morning at work, I get this error.
I have similar different mail merge documents that rely on this code. They all exhibit this behaviour.

If I try to open the mail merge document, unlike working with them at home last night, I get an error opening them ... "An operation cannot be completed because of database engine errors."
Checking on the DataLink Properties, I notice that the Data Source is referring to an old nonexistent file. A check with the "Test Connection" came up with this: "Test connection failed because of an error in initializing provider. Unspecified error"
I manually directed the mailmerge to an appropriate datasource. The merge completed, wrong information in it though, but that may be because I hadn't isolated the recipients. I saved the report and tried executing the code to open it, but got the same initial message.

This is a major setback for my application and my mail merge documents (for a novice) are complex. I am really hesitant to monkey around with stuff I have no idea about fearing damaging the work that so many have helped me with.

I hope someone will be kind enough to walk me through a diagnosis (using information they need to help track it down) to resolve this.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel to word Mail Merge problem	mark_kofi	Mail Merge	4	07-17-2018 03:49 PM
Mail Merge View problem	drgt	Mail Merge	2	02-14-2017 04:31 AM
Mail Merge Problem	Hook	Mail Merge	2	01-11-2012 09:36 AM
Mail Merge Problem	JohnRG	Mail Merge	0	11-02-2009 02:50 AM
problem in Mail merge	rjagathe	Mail Merge	0	08-12-2009 08:25 AM