• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 792
  • Last Modified:

Use Access 2010 to create a mailmerge with Word 2010

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

  • 3
  • 2
1 Solution
<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
OdeMonkeyAuthor Commented:
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

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    
OdeMonkeyAuthor Commented:

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!

No Biggie.

Glad you got it cased

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now