We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Use Access 2010 to create a  mailmerge with Word 2010

Medium Priority
808 Views
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?

Thanks,

Dan
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"
        Else
            ' 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

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2014

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

Author

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

CERTIFIED EXPERT
Most Valuable Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Nick67

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!

Dan
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
No Biggie.

Glad you got it cased
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.