Access / Word 2010 VBA Mail Merge Tries to Open [Foldername].mdb Instead of ACCDB Source

Question

We are attempting to automate a mail merge process from within Access - upon clicking a button, VBA runs that specifies the current database (an accdb) as the data source and runs SQL, per the code below:

'Set up Word
Dim objWord As Object
Set objWord = CreateObject("Word.Application")

'Make visible, open specified doc to merge
With objWord
    .Visible = True
    .Documents.Open strDocName
End With

'Set the data source path
Dim docPath As String
docPath = CurrentProject.Path & "\" & CurrentProject.Name

'Open the merge data source - simplified
objWord.activedocument.mailmerge.opendatasource _
    Name:=docPath, _
    SQLStatement:=strSQL

strDocName and strSQL are passed in and contain valid, functional contents.

It succeeds in opening word, making it visible and opening the template to merge into. At that point, however, it comes up asking to confirm data source, and shows a list of possible data sources. Upon clicking the 'show all' checkbox, I can scroll down to MS Access Database via ODBC (.mdb, .accdb) and select it from the list.

It then presents with a box titled 'ODBC Microsoft Access Driver Login Failed', saying 'Could Not Find File: "[path to database folder]/[name of containing folder].mdb"'

So, if my database were located in C:\Temp the error path would read 'C:\Temp.mdb'. Moving the access database to another folder causes the error path to update looking for an accordingly named mdb file based on the containing folder.

I don't know if it's relevant but it also appears to be opening the template in the mode for editing the template itself, rather than generating a new document from that template.

Am I missing something here or does anyone have any ideas?

Cheers

With the assistance of Remou below, we gave up linking directly to Access and used the code from Remou's suggested link to output to a temporary text file, then merge from there.

We modified the code to remove CurrentBackendPath() and changed the only reference to this function as follows:

Private Function GetStartDirectory() As String
    'GetStartDirectory = CurrentBackendPath() & "mm\"
    GetStartDirectory = CurrentProject.Path & "\mm\"
End Function

The only other modifications we used were to change the code to be specific to our purpose. Thanks once again to everyone for your replies. I would upvote Remou for their response if I had enough reputation to do so!

According to MSDN (msdn.microsoft.com/en-us/library/office/ff841005.aspx), "When retrieving data through Open Database Connectivity (ODBC), you specify a connection string." You appear to be supplying the file path instead of a connection string. — HansUp
Re: templates -- using the Open method opens an existing document, in this case the template. If you want to create a new document, use Documents.Add and pass the path to the template as the first argument. Bear in mind that Documents.Add will return a reference to the newly created document. — Zev Spitz
@HansUp You appear to be correct - I'll be following Remou's response below to try and address this. Thank you for your response! — WaideWalker
@ZevSpitz I'll try switching over to Documents.Add - I had a hunch that particular problem was something along those lines - thanks! — WaideWalker
@ZevSpitz Your suggestion fixed the template problem, thank you. — WaideWalker

Fionnuala Fionnuala · Accepted Answer · 2012-11-01T11:01:32

I would strongly advise you not to link to an MS Access file. Output the data required to a text file and link to that. Linking to Access is all very well for manually controlled mailmerges, but it becomes tedious in the extreme when you wish to automate. You may wish to read http://www.tek-tips.com/faqs.cfm?fid=5088

That being said, recording a macro, I get the following:

ActiveDocument.MailMerge.OpenDataSource Name:="Z:\Docs\Test.accdb", _
    ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
    AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
    WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
    Format:=wdOpenFormatAuto, Connection:= _
    "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=Z:\Docs\Test.accdb;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk" _
    , SQLStatement:="SELECT * FROM `Table1`", SQLStatement1:="", SubType:= _
    wdMergeSubTypeAccess

Most of the above is unnecessary, but you will see that it includes a connection string. This was not the case in earlier versions. You can get by with:

With objWord.ActiveDocument.MailMerge
    .OpenDataSource Name:="Z:\Docs\Test.accdb", _
        ConfirmConversions:=False, LinkToSource:=True, _
        Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\Docs\Test.accdb;" _
        , SQLStatement:="SELECT * FROM `Table1`"
End With

Access / Word 2010 VBA Mail Merge Tries to Open [Foldername].mdb Instead of ACCDB Source

1 Answers