Use Access 2010 to create a  mailmerge with Word 2010

Posted on 2011-04-29
Last Modified: 2012-05-11
I am updating from Office 2003 to Office 2010 and moving from a .mdb to a .accdb. The problem I have encountered is that the code is not able to open the database.  I get this Message "Word was unable to open the data source."  I have confirmed that the .OpenDataSource Name and sqlstatement values are correct and that the document file exists.   I have also tested the SQL to verify that it works and returns records.  I have included below the function that is used to access Word and create the document. This code has worked inheeh earlier version.

What do I need to do to allow Word to access the database?


Public Function docPrinter(inDoc As String, inSQL As String, inDB As String, _
    inCnt As Integer, inPref As Boolean)

' Prints (or opens) the selected document where
'   inDoc is the fully-qualified location of the document to be printed,
'   inSQL is the complete SQL statement that will retrieve the correct records,
'   inDB is the fully-qualified location of the database to be queried,
'   inCnt is the number of copies required (ignored if the document is to be opened), and
'   inPref is true if direct printing is desired (false will open the document in Word).
' Examples:
'    inDoc = "C:\2009 Non-Certified Contract.doc"
'    inSQL = "SELECT * FROM Combined WHERE [Contract Type] in ('N')"
'    inDB = "C:\$User\Business Office\EmployeeContractsData.mdb"
Debug.Print inDoc
Debug.Print inSQL
Debug.Print inDB

    Dim myWord As Object
    Dim i As Integer
    Set myWord = CreateObject("Word.Application")
    myWord.Documents.Open inDoc, , True
    With myWord.ActiveDocument.MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource Name:=inDB, sqlstatement:=inSQL
        .ViewMailMergeFieldCodes = False
        ' Determine the actions to take (Print or Open).
        If inPref Then
            ' Print the document
            .Destination = wdSendToPrinter
            For i = 1 To inCnt
                .Execute pause:=False   'This will cause the output to be collated in complete sets.
            Next i
            docPrinter = "Printing is Done"
            ' Open the document with the merge completed.
            .Destination = wdSendToNewDocument
            .Execute pause:=False
            With myWord.Application
                .DisplayAlerts = wdAlertsNone
                .Visible = True
                .WindowState = wdWindowStateMaximize
            End With
            docPrinter = "Document is open"
        End If
    End With
    myWord.Documents(inDoc).Close savechanges:=wdDoNotSaveChanges
    ' Housekeeping...
    Set myWord = Nothing
End Function

Open in new window

Question by:OdeMonkey
    LVL 26

    Expert Comment

    <Public Function docPrinter(inDoc As String, inSQL As String, inDB As String,  inCnt As Integer, inPref As Boolean)>

    Show us what you are actually passing into this function.
    The code looks okay, but it's only half the puzzle
    LVL 4

    Author Comment

    Here are the values as they arrived in the call to docPrinter:

     inDoc = C:\Contracts\Document Templates\2011\2011 Payroll Start Order with YTD Info.doc
     inSQL = SELECT * FROM Combined WHERE SSN = '275-44-1234'
      inDB = C:\Contracts\Data\EmployeeContractsData2011.accdb
     inCnt = 3
    inPref = True

    LVL 26

    Accepted Solution

    It seems to be INCREDIBLY picky about the SQL statement.
    I mocked up a db to run it, an accdb to be a data source and a docx word file

    This works
    inSQL = "select * from [tbltest]"

    and this does not
    inSQL = "select * from tbltest"

    SELECT * FROM [Combined] WHERE SSN = '275-44-1234'

    I also altered the .OpenDataSource line
    Name:=inDB, Connection:="TABLE tbltest", sqlstatement:=inSQL

    Try to alter that line.
    .OpenDataSource Name:=inDB, Connection:="TABLE Combined", sqlstatement:=inSQL

    If it works, you will need to alter the function and pass the connection string in as a variable    
    LVL 4

    Author Closing Comment


    The brackets around the table names solved the problem!  It does seem VERY picky.  I know it took some work on your part to help with this one.  Sorry it took so long for me to respond.  I have myriad responsibilities and I didn't get to try your suggestion until moments ago.

    Thanks again!

    LVL 26

    Expert Comment

    No Biggie.

    Glad you got it cased

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Nice table. Huge mess. Maybe this was something you created way back before you figured out tabs or a document you received from someone else. Either way, using the spacebar to separate the columns resulted in a mess. Trying to convert text to t…
    Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now