none
Issues when automating mail merge from Access 2016 RRS feed

  • Question

  • Using Access 20106 and Word 2016. I am trying to do an automated mail merge via code in an access form.  (Code pasted below)

    This code occasionally works, however, most of the time, I get an error - ODBC Microsoft Access Driver Login Failed
    Error Message: Could not find file: 'O:\HS\documents.mdb

    The problem is that the file that I am referring to is 'O:\HS\documents\MyDb.accdb' so why is it trying to find the wrong file?

    Very frustrating - I have done VB/Access development since 95, and although I very occasionally code nowadays, these sort of issues should not occur!!

    TIA.

    Sub ExportQueryToWordTemplate(DBPath As String, SQLQuery As String, TemplatePath As String, OutputPath As String, OutputFileName As String)
        Dim oWord As Word.Application
        Dim oWdoc As Word.Document
        Dim wdInputName As String
        Dim wdOutputName As String
        Dim outFileName As String
        
     
        wdInputName = TemplatePath
        wdOutputName = OutputPath & "\" & OutputFileName
        
        Set oWord = New Word.Application
        Set oWdoc = oWord.Documents.Open(wdInputName)
        
        ' Start mail merge
        '------------------------------------------------
        With oWdoc.MailMerge
            .MainDocumentType = wdDirectory
            .OpenDataSource _
                Name:=DBPath, _
                AddToRecentFiles:=False, _
                LinkToSource:=True, _
                Connection:="QUERY mailmerge", _
                ReadOnly:=True, _
                Openexclusive:=False, _
                SQLStatement:=SQLQuery
            
            .Destination = wdSendToNewDocument
            .Execute Pause:=False
        End With
        
        ' Save file as Word Document
        '------------------------------------------------
        oWord.ActiveDocument.SaveAs2 wdOutputName & ".docx", 16
        oWord.Quit savechanges:=False
          
        Set oWord = Nothing
        Set oWdoc = Nothing
    End Sub
    Monday, October 31, 2016 9:13 PM

All replies

  • Hi mercury,

    Do you mean you pass “O:\HS\documents\MyDb.accdb” to DBPath, and you got error “O:\HS\documents.mdb” could not be fount? To check whether you pass a wrong value for the Name, I suggest you use “Debug.Print” to output the DBPath. I suggest you debug your code to check which line cause this error and check the values.

    In addition, is O a local disk or mapped disk? I suggest you try other accdb file from local disks.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, November 1, 2016 5:30 AM